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 )