Get column names of the result of a query Oracle SQL

Question

I need a query that will get the column names of the result of another query. The other query can be anything - I can't make any assumptions about it but it will typically be some SELECT statement.

For example, if I have this table Members

Id | Name | Age
---|------|----
1  | John | 25
2  | Amir | 13

And this SELECT statement SELECT Name, Age FROM Members

Then the result of the query I'm trying to write would be

Name
Age

In SQL Server, there is a function - sys.dm_exec_describe_first_result_set - that does this but I can't find an equivalent in Oracle.

I tried to use this answer but I can't use CREATE TYPE statements because of permissions issues and I probably can't use CREATE FUNCTION statements for the same reason.

Answer 1

Suppose you have a query like this:

select *
from   (select deptno, job, sal from scott.emp)
pivot  (avg(sal) as avg_sal for job in 
           ('ANALYST' as analyst, 'CLERK' as clerk, 'SALESMAN' as salesman)
       )
order by deptno
;

This produces the result:

    DEPTNO ANALYST_AVG_SAL CLERK_AVG_SAL SALESMAN_AVG_SAL
---------- --------------- ------------- ----------------
        10                          1300                 
        20            3000           950                 
        30                           950             1400

Notice the column names (like ANALYST_AVG_SAL) - they don't appear exactly in that form anywhere in the query! They are made up from two separate pieces, put together with an underscore.

Now, if you were allowed to create views (note that this does not create any data in your database - it just saves the text of a query), you could do this:

Create the view (just add the first line of code to what we already had):

create view q201028_vw as
select *
from   (select deptno, job, sal from scott.emp)
pivot  (avg(sal) as avg_sal for job in 
            ('ANALYST' as analyst, 'CLERK' as clerk, 'SALESMAN' as salesman)
       )
order by deptno
;

(Here I assumed you have some way to identify the query, an id like Q201028, and used that in the view name. That is not important, unless you need to do this often and for a large number of queries at the same time.)

Then you can find the column names (and also their order, and - if needed - their data type, etc.) by querying *_TAB_COLUMNS. For example:

select column_id, column_name
from   user_tab_columns
where  table_name = 'Q201028_VW'
order  by column_id
;

 COLUMN_ID COLUMN_NAME         
---------- --------------------
         1 DEPTNO              
         2 ANALYST_AVG_SAL     
         3 CLERK_AVG_SAL       
         4 SALESMAN_AVG_SAL 

Now you can drop the view if you don't need it for anything else.

As an aside: The "usual" way to "save" queries in the database, in Oracle, is to create views. If they already exist as such in your DB, then all you need is the last step I showed you. Otherwise, were is the "other query" (for which you need to find the columns) coming from in the first place?

Answer 2

I would use the dbms_sql package and the following code example should show you how to start:

DECLARE
    cursorID            INTEGER;
    status              INTEGER;
    colCount            INTEGER;
    rowCount            INTEGER;
  description       dbms_sql.desc_tab;
    colType             INTEGER;
    stringValue     VARCHAR2(32676);
    sqlCmd              VARCHAR2(32767);
BEGIN
    -- open cursor
    cursorID := dbms_sql.open_cursor;

    -- parse statement
    dbms_sql.parse(cursorID, 'select * from user_tables', dbms_sql.native);

    -- describe columns
    dbms_sql.describe_columns(cursorID, colCount, description);

    -- cursor close
    dbms_sql.close_cursor(cursorID);

    -- open cursor
    cursorID := dbms_sql.open_cursor;

    -- assemble a new select only using up to 5 the "text" columns
    FOR i IN 1 .. description.COUNT LOOP
        IF (i > 5) THEN
            EXIT;
        END IF;
        IF (description(i).col_type IN (1, 112)) THEN
            IF (sqlCmd IS NOT NULL) THEN
                 sqlCmd := sqlCmd || ', ';
            END IF;
            sqlCmd := sqlCmd || description(i).col_name;
        END IF;
    END LOOP;
    sqlCmd := 'SELECT ' || sqlCmd || ' FROM user_tables';
    dbms_output.put_line(sqlCmd);

    -- parse statement
    dbms_sql.parse(cursorID, sqlCmd, dbms_sql.native);

    -- describe columns
    dbms_sql.describe_columns(cursorID, colCount, description);

    -- define columns
    FOR i IN 1 .. description.COUNT LOOP
        dbms_sql.define_column(cursorID, i, stringValue, 4000);
    END LOOP;

    -- execute
    status := dbms_sql.execute(cursorID);

    -- fetch up to 5 rows
    rowCount := 0;
    WHILE (dbms_sql.fetch_rows(cursorID) > 0) LOOP
        rowCount := rowCount + 1;
        IF (rowCount > 5) THEN
            EXIT;
        END IF;
        dbms_output.put_line('row # ' || rowCount);
        FOR i IN 1 .. description.COUNT LOOP
            dbms_sql.column_value(cursorID, i, stringValue);
            dbms_output.put_line('column "' || description(i).col_name || '" = "' || stringValue || '"');
        END LOOP;
    END LOOP;

    -- cursor close
    dbms_sql.close_cursor(cursorID);
END;
/

Answer 3

As astentx suggested, you can use a common table expression function to package the PL/SQL code into a SQL statement. This solution is just a single SQL statement, and requires no non-default privileges and does not create any permanent objects.

(The only downside is that not all SQL tools understand these kinds of WITH clauses, and they may throw an error expecting a different statement terminator.)

SQL> create table members(id number, name varchar2(100), age number);

Table created.

SQL> with function get_result_column_names(p_sql varchar2) return sys.odcivarchar2list is
  2      v_cursor_id integer;
  3      v_col_cnt integer;
  4      v_columns dbms_sql.desc_tab;
  5  v_column_names sys.odcivarchar2list := sys.odcivarchar2list();
  6  begin
  7      v_cursor_id := dbms_sql.open_cursor;
  8      dbms_sql.parse(v_cursor_id, p_sql, dbms_sql.native);
  9      dbms_sql.describe_columns(v_cursor_id, v_col_cnt, v_columns);
 10
 11      for i in 1 .. v_columns.count loop
 12  v_column_names.extend;
 13  v_column_names(v_column_names.count) := v_columns(i).col_name;
 14      end loop;
 15
 16      dbms_sql.close_cursor(v_cursor_id);
 17
 18  return v_column_names;
 19  exception when others then
 20      dbms_sql.close_cursor(v_cursor_id);
 21      raise;
 22  end;
 23  select *
 24  from table(get_result_column_names(q'[select name, age from members]'));
 25  /

COLUMN_VALUE
--------------------------------------------------------------------------------
NAME
AGE