Universal Data Printer in PL/SQL

The listing below demonstrates two procedures which are to execute any sort of SQL and print the data set in ";" seperated format. Both procedures use DBMS_SQL package for describing and fetching the data. The difference between those procedures is that the second one opens the data set by using a weak ref cursor then the ref cursor is casted to a DBMS_SQL cursor. This feature is announced with Oracle 11g Release 1.

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning option

SQL> create or replace procedure pr_universal_data_printer(p_sql in varchar2) is
  2    fetch_out_of_seq exception;
  3    pragma exception_init(fetch_out_of_seq, -1002);
  4 
  5    unsupported_type exception;
  6    pragma exception_init(unsupported_type, -20001);
  7 
  8    desc_tab dbms_sql.desc_tab2;
  9    cols     binary_integer;
 10    cursor_  binary_integer;
 11 
 12    date_col date;
 13    char_col varchar2(4000);
 14    num_col  number;
 15    exe      number;
 16  begin
 17    if dbms_sql.is_open(cursor_) then
 18      dbms_sql.close_cursor(cursor_);
 19    end if;
 20 
 21    cursor_ := dbms_sql.open_cursor;
 22 
 23    dbms_sql.parse(cursor_, p_sql, dbms_sql.native);
 24 
 25    dbms_sql.describe_columns2(cursor_, cols, desc_tab);
 26 
 27    exe := dbms_sql.execute(cursor_);
 28 
 29    for i in 1 .. cols loop
 30      --print header
 31      dbms_output.put(desc_tab(i).col_name || ';');
 32 
 33      --define columns
 34      case desc_tab(i).col_type
 35        when dbms_types.typecode_varchar then
 36          dbms_sql.define_column(cursor_, i, char_col, 4000);
 37        when dbms_types.typecode_varchar2 then
 38          dbms_sql.define_column(cursor_, i, char_col, 4000);
 39        when dbms_types.typecode_char then
 40          dbms_sql.define_column(cursor_, i, char_col, 4000);
 41        when dbms_types.typecode_number then
 42          dbms_sql.define_column(cursor_, i, num_col);
 43        when dbms_types.typecode_date then
 44          dbms_sql.define_column(cursor_, i, date_col);
 45        else
 46          raise unsupported_type;
 47      end case;
 48    end loop;
 49 
 50    dbms_output.new_line;
 51 
 52    --fetch row
 53    while dbms_sql.fetch_rows(cursor_) > 0 loop
 54      --assign values according to the column types
 55      for i in 1 .. cols loop
 56        case desc_tab(i).col_type
 57          when dbms_types.typecode_varchar then
 58            dbms_sql.column_value(cursor_, i, char_col);
 59            dbms_output.put(replace(char_col,chr(10),'') || ';');
 60          when dbms_types.typecode_varchar2 then
 61            dbms_sql.column_value(cursor_, i, char_col);
 62            dbms_output.put(replace(char_col,chr(10),'') || ';');
 63          when dbms_types.typecode_char then
 64            dbms_sql.column_value(cursor_, i, char_col);
 65            dbms_output.put(replace(char_col,chr(10),'') || ';');
 66          when dbms_types.typecode_number then
 67            dbms_sql.column_value(cursor_, i, num_col);
 68            dbms_output.put(num_col || ';');
 69          when dbms_types.typecode_date then
 70            dbms_sql.column_value(cursor_, i, date_col);
 71            dbms_output.put(to_char(date_col, 'dd/mm/yyyy hh24:mi:ss') || ';');
 72          else
 73            raise unsupported_type;
 74        end case;
 75      end loop;
 76      dbms_output.new_line;
 77    end loop;
 78 
 79    dbms_sql.close_cursor(cursor_);
 80  exception
 81    when fetch_out_of_seq then
 82      dbms_output.new_line;
 83      dbms_output.put_line('End of data.');
 84    when unsupported_type then
 85      dbms_output.new_line;
 86      dbms_output.put_line('Unsupported type in data set.');
 87    when others then
 88      raise;
 89  end pr_universal_data_printer;
 90  /

Procedure created.

SQL> set serveroutput on;
SQL> set autoprint on;
SQL> exec pr_universal_data_printer('select object_name, object_type, status from all_objects where owner =''SYS'' and rownum < 11');
OBJECT_NAME;OBJECT_TYPE;STATUS;                                                
/1000323d_DelegateInvocationHa;JAVA CLASS;VALID;                               
/1000e8d1_LinkedHashMapValueIt;JAVA CLASS;VALID;                               
/1005bd30_LnkdConstant;JAVA CLASS;VALID;                                       
/10076b23_OraCustomDatumClosur;JAVA CLASS;VALID;                               
/100c1606_StandardMidiFileRead;JAVA CLASS;VALID;                               
/10128284_OpenMBeanAttributeIn;JAVA CLASS;VALID;                               
/101419a4_NormalDataCollector;JAVA CLASS;VALID;                                
/1020ed5e_Param;JAVA CLASS;VALID;                                              
/1023e902_OraCharsetUTFE;JAVA CLASS;VALID;                                     
/10297c91_SAXAttrList;JAVA CLASS;VALID;                                        

PL/SQL procedure successfully completed.

SQL> create or replace procedure pr_universal_data_printer2(p_sql in varchar2) is
  2    fetch_out_of_seq exception;
  3    pragma exception_init(fetch_out_of_seq, -1002);
  4 
  5    unsupported_type exception;
  6    pragma exception_init(unsupported_type, -20001);
  7 
  8    weak_ref_cur sys_refcursor;
  9 
 10    desc_tab dbms_sql.desc_tab2;
 11    cols     binary_integer;
 12    cursor_  binary_integer;
 13 
 14    date_col date;
 15    char_col varchar2(4000);
 16    num_col  number;
 17    exe      number;
 18  begin
 19    if weak_ref_cur%isopen then
 20      close weak_ref_cur;
 21    end if;
 22 
 23    open weak_ref_cur for p_sql;
 24 
 25    cursor_ := dbms_sql.to_cursor_number(weak_ref_cur);
 26 
 27    dbms_sql.describe_columns2(cursor_, cols, desc_tab);
 28 
 29    for i in 1 .. cols loop
 30      --print header
 31      dbms_output.put(desc_tab(i).col_name || ';');
 32 
 33      --define columns
 34      case desc_tab(i).col_type
 35        when dbms_types.typecode_varchar then
 36          dbms_sql.define_column(cursor_, i, char_col, 4000);
 37        when dbms_types.typecode_varchar2 then
 38          dbms_sql.define_column(cursor_, i, char_col, 4000);
 39        when dbms_types.typecode_char then
 40          dbms_sql.define_column(cursor_, i, char_col, 4000);
 41        when dbms_types.typecode_number then
 42          dbms_sql.define_column(cursor_, i, num_col);
 43        when dbms_types.typecode_date then
 44          dbms_sql.define_column(cursor_, i, date_col);
 45        else
 46          raise unsupported_type;
 47      end case;
 48    end loop;
 49 
 50    dbms_output.new_line;
 51 
 52    --fetch row
 53    while dbms_sql.fetch_rows(cursor_) > 0 loop
 54      --assign values according to the column types
 55      for i in 1 .. cols loop
 56        case desc_tab(i).col_type
 57          when dbms_types.typecode_varchar then
 58            dbms_sql.column_value(cursor_, i, char_col);
 59            dbms_output.put(replace(char_col,chr(10),'') || ';');
 60          when dbms_types.typecode_varchar2 then
 61            dbms_sql.column_value(cursor_, i, char_col);
 62            dbms_output.put(replace(char_col,chr(10),'') || ';');
 63          when dbms_types.typecode_char then
 64            dbms_sql.column_value(cursor_, i, char_col);
 65            dbms_output.put(replace(char_col,chr(10),'') || ';');
 66          when dbms_types.typecode_number then
 67            dbms_sql.column_value(cursor_, i, num_col);
 68            dbms_output.put(num_col || ';');
 69          when dbms_types.typecode_date then
 70            dbms_sql.column_value(cursor_, i, date_col);
 71            dbms_output.put(to_char(date_col, 'dd/mm/yyyy hh24:mi:ss') || ';');
 72          else
 73            raise unsupported_type;
 74        end case;
 75      end loop;
 76      dbms_output.new_line;
 77    end loop;
 78 
 79    dbms_sql.close_cursor(cursor_);
 80  exception
 81    when fetch_out_of_seq then
 82      dbms_output.new_line;
 83      dbms_output.put_line('End of data.');
 84    when unsupported_type then
 85      dbms_output.new_line;
 86      dbms_output.put_line('Unsupported type in data set.');
 87    when others then
 88      raise;
 89  end pr_universal_data_printer2;
 90  /

Procedure created.

SQL>  exec pr_universal_data_printer2('select object_name, object_type, status from all_objects where owner =''SYS'' and rownum < 11');
OBJECT_NAME;OBJECT_TYPE;STATUS;                                                
/1000323d_DelegateInvocationHa;JAVA CLASS;VALID;                               
/1000e8d1_LinkedHashMapValueIt;JAVA CLASS;VALID;                               
/1005bd30_LnkdConstant;JAVA CLASS;VALID;                                       
/10076b23_OraCustomDatumClosur;JAVA CLASS;VALID;                               
/100c1606_StandardMidiFileRead;JAVA CLASS;VALID;                               
/10128284_OpenMBeanAttributeIn;JAVA CLASS;VALID;                               
/101419a4_NormalDataCollector;JAVA CLASS;VALID;                                
/1020ed5e_Param;JAVA CLASS;VALID;                                              
/1023e902_OraCharsetUTFE;JAVA CLASS;VALID;
/10297c91_SAXAttrList;JAVA CLASS;VALID;                                        

PL/SQL procedure successfully completed.