PERL: Utility to sort and remove duplicates from a csv file.

Here is a little PERL Script that will take a file and sort the data lines and remove duplicates.

#!/usr/bin/perl

$ifile=$ARGV[0];
$ofile=$ARGV[1];

$header=`sed -n '1p' $ifile` ;
$data=`sed '1d' $ifile | sort -u` ;
open(my $fh, '>', $ofile) or die "Could not open file '$ofile' $!";
print $fh $header;
print $fh $data;

close $fh;
exit 0

RegEx: Searching for CFQUERY tags that are not Query of Queries

I recently had to search several thousand files for ColdFusion <CFQUERY> tags that were hitting a database and not the dbtype="query". The following regex pattern in Sublime will do the trick

^(?!.*dbtype.*)(<cfquery .*>)

Oracle: Looking at the cursor metadata

Based on information provided at https://boobalganesan.blogspot.com/2017/08/dbmssqldescribecolumns-tips.html here are a few steps for dumping out cursor meta data returned from a stored proc.

first, create a global temp table

 CREATE GLOBAL TEMPORARY TABLE desc_col_test
    (
    col_id NUMBER(10),
    col_desc VARCHAR2(255)
);

then run this block

declare
    
   O_RES SYS_REFCURSOR;

    v_cursor_number number;
    v_columns number;
    v_desc_tab dbms_sql.desc_tab;

begin
  
delete from desc_col_test;

 --Adust to your variables accordingly 
YOUR_SCHEMA.YOUR_PKG_YOUR_PROD(p_dt => TO_DATE ('31-12-2018 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), o_res => O_RES);

v_cursor_number := dbms_sql.to_cursor_number(rc => O_RES);
    --Get information on the columns
    dbms_sql.describe_columns(v_cursor_number, v_columns, v_desc_tab);
--Loop through all the columns, find COLUMN1 position and type
    for i in 1 .. v_desc_tab.count loop
      
INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_name:'||decode(v_desc_tab(i).col_name, Null, 'Null', v_desc_tab(i).col_name));

INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_type: ' ||
 case
                 when v_desc_tab(i).col_type = 1 then 'VARCHAR2'
                 when v_desc_tab(i).col_type = 2 then 'NUMBER'
                 when v_desc_tab(i).col_type = 8 then 'LONG'
                 when v_desc_tab(i).col_type =  9 then 'VARCHAR'
                 when v_desc_tab(i).col_type =  12 then 'DATE'
                 when v_desc_tab(i).col_type =  23 then 'RAW'
                 when v_desc_tab(i).col_type =  69 then 'ROWID'
                 when v_desc_tab(i).col_type =  96 then 'CHAR'
                 when v_desc_tab(i).col_type =  100 then 'BINARY_FLOAT'
                 when v_desc_tab(i).col_type =  101 then 'BINARY_DOUBLE'
                 when v_desc_tab(i).col_type =  105 then 'MLSLABEL'
                 when v_desc_tab(i).col_type =  106 then 'MLSLABEL'
                 when v_desc_tab(i).col_type =  112 then  'CLOB'
                 when v_desc_tab(i).col_type =  113 then 'BLOB'
                 when v_desc_tab(i).col_type =  114 then 'BFILE'
                 when v_desc_tab(i).col_type =  115 then 'CFILE'
                 when v_desc_tab(i).col_type =  178 then 'TIME'
                 when v_desc_tab(i).col_type =  179 then 'TIME WITH TIME ZONE'
                 when v_desc_tab(i).col_type =  180 then 'TIMESTAMP'
                 when v_desc_tab(i).col_type =  181 then 'TIMESTAMP WITH TIME ZONE'
                 when v_desc_tab(i).col_type =  231 then 'TIMESTAMP WITH LOCAL TIME ZONE'
                 when v_desc_tab(i).col_type =  182 then 'INTERVAL YEAR TO MONTH'
                 when v_desc_tab(i).col_type =  183 then 'INTERVAL DAY TO SECOND'
                 else 'OTHER' end);


 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_precision:'||decode(v_desc_tab(i).col_precision, Null, 'Null', v_desc_tab(i).col_precision));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_max_len:'||decode(v_desc_tab(i).col_max_len, Null, 'Null', v_desc_tab(i).col_max_len));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_name_len:'||decode(v_desc_tab(i).col_name_len, Null, 'Null', v_desc_tab(i).col_name_len));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_schema_name:'||decode(v_desc_tab(i).col_schema_name, Null, 'Null', v_desc_tab(i).col_schema_name));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_schema_name_len:'||decode(v_desc_tab(i).col_schema_name_len, Null, 'Null', v_desc_tab(i).col_schema_name_len));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_scale:'||decode(v_desc_tab(i).col_scale, Null, 'Null', v_desc_tab(i).col_scale));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_charsetid:'||decode(v_desc_tab(i).col_charsetid, Null, 'Null', v_desc_tab(i).col_charsetid));

 INSERT INTO desc_col_test (col_id, col_desc) VALUES (i, 'col_charsetform:'||decode(v_desc_tab(i).col_charsetform, Null, 'Null', v_desc_tab(i).col_charsetform));

    end loop;

 
end;
/

SELECT *
FROM
  (SELECT col_id,
    regexp_substr(col_desc,'[^:]+',1,1) col_desc_name,
    regexp_substr(col_desc,'[^:]+',1,2) col_desc_value
  FROM desc_col_test
  ) pivot (MAX(col_desc_value) FOR col_desc_name IN ('col_name' AS COL_NAME, 'col_type' AS COL_TYPE, 'col_precision' AS COL_PRECISION,
  'col_max_len' AS COL_MAX_LEN, 'col_name_len' AS COL_NAME_LEN,
  'col_schema_name' AS COL_SCHEMA_NAME, 'col_schema_name_len' AS COL_SCHEMA_NAME_LEN,
  'col_scale' AS COL_SCALE, 'col_charsetid' AS COL_CHARSETID,
  'col_charsetform' AS COL_CHARSETFORM, 'Nullable' AS NULLABLE))
ORDER BY col_id;

How to Make an Ugly Excel Document: Otherwise known as Unit Testing POIUtility 4.0 (Apache POI 4.0.1) compatible

I've been working on a revamp of Ben Nadel's excellent but aging POIUtility for the generation of Excel files.   I've been needing XLSX support so I updated the code to use Apache POI 4.0.1 and embedded Mark Mandel's JavaLoader and the needed packages for it to work standalone without java lib dependencies on the server.

Here's a summary of what I've done so far;

• Uses Apache POI 4.0.1 with JavaLoader
• Supports creation of both XLS and XLSX
• RowSpan support.  Either by itself or with colSpan to create a range (see ugly image below)
• poi:row update attribute when working with existing templates.
• refactored the CSSRule.cfc  into cfscript, 

TODO: Work on updating the POIUtility.cfc now that the groundwork is complete.

I did yank the coldfusion_mx_7 folder, figured it's about time for people to move ahead on that one.

Tested on CommandBox with ColdFusion 2018

Go to my repo or the code https://github.com/wiggick/POIUtility.cfc

Side by side results of XLS and XLST Generation
Color, fill pattern, and region test.  (note text colors are just random selection )

Crystal Report Tricks: Using special fields in formulas

When you want to use a special field in Crystal Reports in a formula, you can specify the special field  by using the special field name without spaces.  I admit I haven't tried them all, but in this case, the comments are multi-line and I wanted to grab just the first line.

split(ReportComments,chr(13))[1]

Oracle Tricks: Getting a range of numbers

Another method for getting a range of dates from Oracle PL/SQL

select last_day(to_date(to_char(months.month) || '/1/' ||
                        to_char(years.year),
                        'mm/dd/yyyy')) as range_EOM
  from (select rownum year from dual connect by level <= 2019) years,
       (select rownum month from dual connect by level <= 12) months
 where years.year >= 2000
   and months.month >= 1

Grabbing UTC Hour from Oracle

A simple sql script for obtaining gmt hour from Oracle

select sys_extract_utc(systimestamp) as UTC,
extract(hour from sys_extract_utc(systimestamp) ) as UTC_HOUR,
extract(hour from cast(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as timestamp) ) as  local_hour
from dual