1

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.

Markus
  • 763
  • 7
  • 24
  • You should know them, because you have specified the query. Don't use `*`. Be explicit. – ceving Aug 17 '16 at 08:35
  • Not necessarily: What if I use the following query? `SELECT * FROM TBL_A A, TBL_B B WHERE A.ID = B.ID` – Markus Aug 17 '16 at 08:37
  • See here how to list the columns of a table: http://stackoverflow.com/a/26977159/402322 – ceving Aug 17 '16 at 08:38
  • If you explain a little more how you plan on using this, or even better, if you can provide some code of where you need this, you may get an answer better suited to your needs. For instance, is it possible that you are looking for a way of getting this information using the JDBC API? – sstan Aug 17 '16 at 10:37
  • I have very lengthy SQL stmts and I want to get the fetched columns. – Markus Aug 17 '16 at 11:30

1 Answers1

4

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;

Update: In order to get a result set you can do this:

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(30);

CREATE OR REPLACE FUNCTION GetColumns(sqlstr IN VARCHAR2) 
   RETURN VARCHAR_TABLE_TYPE PIPELINED AS

    cur INTEGER;
    columnCount INTEGER;
    describeColumns DBMS_SQL.DESC_TAB;

BEGIN 

    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS(cur, columnCount, describeColumns);   
    FOR i IN 1..columnCount LOOP
        PIPE ROW(describeColumns(i).col_name);
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(cur);
   RETURN;
END;


SELECT * FROM TABLE(GetColumns('SELECT * FROM TBL_A A, TBL_B B WHERE A.ID = B.ID'));
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110