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;
