set serveroutput on
declare
sql_stmt varchar2(32000) := 'select ''Hello world'' "A text", 42 a_number, dummy from dual';
c number;
cntCols number;
cols dbms_sql.desc_tab2;
datatype varchar2(8);
begin
dbms_output.new_line;
c := dbms_sql.open_cursor;
dbms_sql.parse(c, sql_stmt, dbms_sql.native);
dbms_sql.describe_columns2(c, cntCols, cols);
for i in 1 .. cntCols loop
datatype := case cols(i).col_type
when dbms_sql.varchar2_type then 'varchar2'
when dbms_sql.char_type then 'char'
when dbms_sql.number_type then 'number'
else to_char(cols(i).col_type)
end;
dbms_output.put_line(' ' || rpad(cols(i).col_name, 10) || datatype);
end loop;
dbms_sql.close_cursor(c);
end;
/
Four versions of desc_rec
There are four versions of the describe_columns procedure: describe_columns, describe_columns2, describe_columns3 and describe_columns4. Each of these returns a corresponding desc_tab … desc_tab4 which in turn is a table of a desc_rec … desc_rec4 structure.
Differences of the desc_rec structures
The fields of the desc_rec structures are
Field name
Differences
col_type
col_max_len
col_name
Differs in length (desc_rec: varchar2(32), other three varchar2(32767))