Here's the text of a report:
/* http://stackoverflow.com/questions/6544922/column-names-in-an-empty-oracle-ref-cursor */
DECLARE
v_ref_cur SYS_REFCURSOR;
v_cur_handle NUMBER;
v_count NUMBER;
v_desc_tab dbms_sql.desc_tab;
v_statement clob := :a_statement;
v_print_data_type pls_integer := :a_print_data_type;
DELIMITER constant varchar2( 5 char ) := chr( 13 ) || chr( 10 ) || chr( 9 ) || ', ';
PROCEDURE print_desc_tab( a_desc_tab IN sys.dbms_sql.desc_tab, a_print_data_type in pls_integer ) as
v_data_type VARCHAR2(30);
v_delimiter varchar2( 30 char ) := '';
BEGIN
dbms_output.put_line( '<pre>' );
FOR i IN 1 .. a_desc_tab.count LOOP
SELECT DECODE( to_char( a_desc_tab( i ).col_type ), 1, 'VARCHAR2', 2, 'NUMBER', 12, 'DATE' )
INTO v_data_type
FROM dual
;
dbms_output.put( v_delimiter || a_desc_tab( i ).col_name );
if ( 1 = a_print_data_type ) then
dbms_output.put( ' ' || v_data_type);
case a_desc_tab( i ).col_type
when 1 then
dbms_output.put( '(' || to_char( a_desc_tab( i ).col_max_len ) || ' char)' );
when 2 then
if ( 0 != a_desc_tab( i ).col_precision ) then
dbms_output.put(
'(' || to_char( a_desc_tab( i ).col_precision ) || ', ' || to_char( a_desc_tab( i ).col_scale ) || ')'
);
end if;
else
null;
end case;
end if;
v_delimiter := DELIMITER;
END LOOP;
dbms_output.new_line;
dbms_output.put_line( '</pre>' );
END print_desc_tab;
BEGIN
OPEN v_ref_cur FOR v_statement;
v_cur_handle := dbms_sql.to_cursor_number( v_ref_cur );
dbms_sql.describe_columns( v_cur_handle, v_count, v_desc_tab );
print_desc_tab( v_desc_tab, v_print_data_type );
dbms_sql.close_cursor( v_cur_handle );
END;
And here's the link for user report (hosted on Google drive). Once downloaded it can be imported into SQLDeveloper.
