identify the columns and data types in a Weakly Typed REF CURSOR

Reference : http://www.morganslibrary.com/hci/hci003.html

I can't even count the number of times I have found myself staring at the output of a pipelined table function, or a weakly typed ref cursor, and wondered about the column names, data types, and other attributes.

If you have faced this then you too can stop cursing the darkness. Heres a few demonstrations of the pain.
SELECT COUNT(*)
FROM dba_arguments
WHERE data_type = 'REF CURSOR';

SELECT DISTINCT package_name
FROM dba_arguments
WHERE data_type = 'REF CURSOR'
AND package_name LIKE 'DBMS%'
ORDER BY 1;
And that isn't counting all of the code written by internal developers and developers from software vendors. Lets start by building a demo table and loading it with a few rows.
CREATE TABLE employees (
empid NUMBER(5),
empname VARCHAR2(30));

INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;

SELECT * FROM employees;
Then we will create a small stored procedure that uses a weakly typed ref cursor to receive input from an anonymous block.
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor IN SYS_REFCURSOR) IS
TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;

rec_array array_t;
BEGIN
FETCH p_cursor BULK COLLECT INTO rec_array;

FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i));
END LOOP;
END pass_ref_cur;
/
Lets test the anonymous block and see if it all works.
set serveroutput on

DECLARE
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR
'SELECT empname FROM employees';

pass_ref_cur(rec_array);
CLOSE rec_array;
END;
/
The block calls the pass_ref_cursor procedure which uses dbms_output to show us that it read the employee table.

Here we can see the source code and can identify the source and its data type. But what if we couldn't?

The following code demonstrates the full capability of the DBMS_SQL.DESC_TAB and takes advantage of the fact that with Database 11g we can convert ref cursors into DBMS_SQL API handles. The private procedure "p" is used to print the results using DBMS_OUTPUT.
CREATE OR REPLACE PROCEDURE print_desc_tab(desctab IN sys.dbms_sql.desc_tab)
AUTHID CURRENT_USER AS
PROCEDURE p (b in varchar2) IS
BEGIN
dbms_output.put_line(SUBSTR(b,1,255));
END p;
BEGIN
FOR i IN 1 .. desctab.count LOOP
p(rpad('-', 30, '-'));
p(rpad('COL_TYPE:',30)||to_char(desctab(i).col_type));
p(rpad('COL_MAX_LEN:',30)||to_char(desctab(i).col_max_len));
p(rpad('COL_NAME:',30)||desctab(i).col_name);
p(rpad('COL_NAME_LEN:',30)||to_char(desctab(i).col_name_len));
p(rpad('COL_SCHEMA_NAME:',30)||desctab(i).col_schema_name);
p(rpad('COL_SCHEMA_NAME_LEN:',30)||to_char(desctab(i).col_schema_name_len));
p(rpad('COL_PRECISION:',30)||to_char(desctab(i).col_precision));
p(rpad('COL_SCALE:',30)||to_char(desctab(i).col_scale));
p(rpad('COL_CHARSETID:',30)||to_char(desctab(i).col_charsetid));
p(rpad('COL_CHARSETFORM:',30)||to_char(desctab(i).col_charsetform));

IF desctab(i).col_null_ok THEN
p(rpad('COL_NULL_OK:',30)||'TRUE');
ELSIF NOT desctab(i).col_null_ok THEN
p(rpad('COL_NULL_OK:',30)||'FALSE');
ELSE
p(rpad('COL_NULL_OK:',30)||'NULL');
END IF;
END LOOP;
END print_desc_tab;
/
Lets run an anonymous block that calls the print_desc_tab and test it.
DECLARE
refcur SYS_REFCURSOR;
h NUMBER;
cnt NUMBER;
ret dbms_sql.desc_tab;
BEGIN
OPEN refcur FOR
SELECT * FROM employees;

h := dbms_sql.to_cursor_number(refcur);

dbms_sql.describe_columns(h, cnt, ret);
print_desc_tab(ret);
dbms_sql.close_cursor(h);
END demo;
/
It works well. the DBMS_SQL's DESCRIBE_COLUMNS API gives us access to the column data type in integer form, its max length in bytes, the column's name, the length of the column's name in bytes, the schema name that owns the column's data type if the data type is user defined, the length, in bytes, of the schema name, and the columns precision, scale, character set identifier, character set form, and whether it is nullable.

Lets try again but this time with a simplified version of print_desc_tab that only exposes two attributes: data type and column name.
CREATE OR REPLACE PROCEDURE print_desc_tab(desctab IN sys.dbms_sql.desc_tab)
AUTHID CURRENT_USER AS
dtype VARCHAR2(30);

PROCEDURE p (b in varchar2) IS
BEGIN
dbms_output.put_line(SUBSTR(b,1,255));
END p;
BEGIN
FOR i IN 1 .. desctab.count LOOP
SELECT DECODE(to_char(desctab(i).col_type), 1, 'VARCHAR2', 2, 'NUMBER', 12, 'DATE')
INTO dtype
FROM dual;

p(rpad('-', 30, '-'));
p(rpad('COL_TYPE:',30) || dtype);
p(rpad('COL_NAME:',30) || desctab(i).col_name);
END LOOP;
END print_desc_tab;
/
We will again run the same anonymous block to call print_desc_tab
DECLARE
refcur SYS_REFCURSOR;
h NUMBER;
cnt NUMBER;
ret dbms_sql.desc_tab;
BEGIN
OPEN refcur FOR
SELECT * FROM employees;

h := dbms_sql.to_cursor_number(refcur);

dbms_sql.describe_columns(h, cnt, ret);
print_desc_tab(ret);
dbms_sql.close_cursor(h);
END demo;
/
Now you know how to do it.The only thing left is to find a project where you can put it to good use.