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.
No comments:
Post a Comment