Markus Markus - 3 months ago 11
SQL Question

List all columns of a query (Oracle)

Is there a SQL stmt in Oracle that lets me extract all the columns a query returns?

In conjunction with Java I could execute the query and process the metadata but I look for a handy way.

Answer

Try this one:

DECLARE 

    sqlstr VARCHAR2(1000);
    cur INTEGER;
    columnCount INTEGER;
    describeColumns DBMS_SQL.DESC_TAB;

BEGIN 

    cur := DBMS_SQL.OPEN_CURSOR;
    sqlstr := 'SELECT * FROM TBL_A A, TBL_B B WHERE A.ID = B.ID';
    DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS(cur, columnCount, describeColumns);   
    FOR i IN 1..columnCount LOOP
        DBMS_OUTPUT.PUT_LINE ( describeColumns(i).col_name );
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(cur);

END;