Column definitions of CTE

Question

How to get the column definitions of a CTE in Oracle?

DESCRIBE DUAL yields the column definition of DUAL. But:

WITH MyTable AS (SELECT * FROM DUAL)
DESCRIBE MyTable;

gives an error: missing SELECT keyword

Answer 1

code error says, that there are missing select clause after the with block. describe command shows the structure of the TABLE, not the structure of QUERY RESULT. To use describe you should "materialize" your output first.

create table tab1 as (
  with q1 as (select * from tab)
  select * from q1
);
describe tab1;

Or you can use dump() function, but it should we explicitly added for each column.

select dump(col1), dump(col2)
from query;

Some information here:

Get data type of field in select statement in ORACLE

Answer 2

You can find the column names and types of a SQL statement using DBMS_SQL (as Alex Yu suggested).

You cannot only use a CTE. To make the SQL statement valid you must select from that CTE, but that's a trivial change.

declare
    v_sql           varchar2(4000) := 'WITH MyTable AS (SELECT * FROM DUAL) select * from mytable';
    v_cursor_number integer;
    v_column_count  number;
    v_columns       dbms_sql.desc_tab3;
begin
    --Parse statement, get columns.
    v_cursor_number := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor_number, v_sql, dbms_sql.native);
    dbms_sql.describe_columns3(v_cursor_number, v_column_count, v_columns);

    --Print metadata.
    dbms_output.put_line('Column Name,Column Type');
    for i in 1 .. v_column_count loop
        dbms_output.put_line(v_columns(i).col_name || ',' ||
            --Thanks to APC for providing this list in
            --https://stackoverflow.com/a/12041206/409172:
            case v_columns(i).col_type
                when dbms_types.TYPECODE_DATE then 'DATE'             
                when dbms_types.TYPECODE_NUMBER then 'NUMBER'           
                when dbms_types.TYPECODE_RAW then 'RAW'              
                when dbms_types.TYPECODE_CHAR then 'CHAR'             
                when dbms_types.TYPECODE_VARCHAR2 then 'VARCHAR2'         
                when dbms_types.TYPECODE_VARCHAR then 'VARCHAR'          
                when dbms_types.TYPECODE_MLSLABEL then 'MLSLABEL'         
                when dbms_types.TYPECODE_BLOB then 'BLOB'             
                when dbms_types.TYPECODE_BFILE then 'BFILE'            
                when dbms_types.TYPECODE_CLOB then 'CLOB'              
                when dbms_types.TYPECODE_CFILE then 'CFILE'            
                when dbms_types.TYPECODE_TIMESTAMP then 'TIMESTAMP'        
                when dbms_types.TYPECODE_TIMESTAMP_TZ then 'TIMESTAMP_TZ'     
                when dbms_types.TYPECODE_TIMESTAMP_LTZ then 'TIMESTAMP_LTZ'    
                when dbms_types.TYPECODE_INTERVAL_YM then 'INTERVAL_YM'      
                when dbms_types.TYPECODE_INTERVAL_DS then 'INTERVAL_DS'      
                when dbms_types.TYPECODE_REF then 'REF'              
                when dbms_types.TYPECODE_OBJECT then 'OBJECT'           
                when dbms_types.TYPECODE_VARRAY then 'VARRAY'                       
                when dbms_types.TYPECODE_TABLE then 'TABLE'                        
                when dbms_types.TYPECODE_NAMEDCOLLECTION then 'NAMEDCOLLECTION'  
                when dbms_types.TYPECODE_OPAQUE then 'OPAQUE'                            
                when dbms_types.TYPECODE_NCHAR then 'NCHAR'            
                when dbms_types.TYPECODE_NVARCHAR2 then 'NVARCHAR2'       
                when dbms_types.TYPECODE_NCLOB then 'NCLOB'                  
                when dbms_types.TYPECODE_BFLOAT then 'BFLOAT'           
                when dbms_types.TYPECODE_BDOUBLE then 'BDOUBLE'          
                when dbms_types.TYPECODE_UROWID then 'UROWID'              
            end
        );
    end loop;

    --Close the cursor.
    dbms_sql.close_cursor(v_cursor_number);
end;
/

Results:

Column Name,Column Type
DUMMY,VARCHAR