Monday, April 22, 2019

Oracle: Looking at the cursor metadata

Based on information provided at here are a few steps for dumping out cursor meta data returned from a stored proc.

first, create a global temp table

    col_id NUMBER(10),
    col_desc VARCHAR2(255)

then run this block


    v_cursor_number number;
    v_columns number;
    v_desc_tab dbms_sql.desc_tab;

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: ' ||
                 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;


  (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;

Friday, April 5, 2019

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

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

Friday, March 29, 2019

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.


Friday, March 22, 2019

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/' ||
                        '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

Friday, March 1, 2019

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

Wednesday, February 20, 2019

ColdFusion POI:Row and updating

I was working with the POI custom tags from Ben Nadel and noticed that the tag was creating the rows rather than updating. Adding a get and check fixed the issue.
<!--- Try Getting the  row. --->
  <cfset VARIABLES.Row = VARIABLES.SheetTag.Sheet.getRow( JavaCast( "int", (VARIABLES.SheetTag.RowIndex - 1) ) )>

  <cfif ! StructKeyExists(VARIABLES, "Row")>
  <cfset VARIABLES.Row = VARIABLES.SheetTag.Sheet.CreateRow(
   JavaCast( "int", (VARIABLES.SheetTag.RowIndex - 1) )
   ) />

Friday, February 8, 2019

KornShell: Replace spaces with underscores

A simple ksh that will replace a space with "_"

find /your/file/directory -type f -name "* *" | \
while read file; do \
newfile="$(echo "$file" | sed 's/ /_/g')"
mv "${file}" "${newfile}"