Get column names of the result of a query Oracle SQL-CodePudding

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.

CodePudding user response：

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?

CodePudding user response：

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;
/