Pl / SQL- Get column names from a query

Question

I am using Pl / SQL with Oracle Database 11g.

I am writing a function that takes a select statement as a parameter (varchar2). The function uses a for loop to move through the rows and apply formatting to specific columns and prints it all. Basically, I need to somehow get the column names so that I can display them at the top. I know that there are different ways for tables, but since this query is passed, all columns may not be selected, aliases can be used, etc.

Is there a way to select column names from this query?
Ideally, something like:
select column_names from (subquery)

Answer 1

I believe you can use DESCRIBE_COLUMNS for this. Just pass the cursor and other required parameters.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1026120

 declare v_sql varchar2(32767) := 'select 1 column1, 2 column2 from dual'; v_cursor_id integer; v_col_cnt integer; v_columns dbms_sql.desc_tab; begin v_cursor_id := dbms_sql.open_cursor; dbms_sql.parse(v_cursor_id, v_sql, dbms_sql.native); dbms_sql.describe_columns(v_cursor_id, v_col_cnt, v_columns); for i in 1 .. v_columns.count loop dbms_output.put_line(v_columns(i).col_name); end loop; dbms_sql.close_cursor(v_cursor_id); exception when others then dbms_sql.close_cursor(v_cursor_id); raise; end; / Output: COLUMN1 COLUMN2 

Answer 2

Based on dseibert's answer , I created a function to use:

 create type cols_name as table of varchar2(32767) / CREATE OR REPLACE FUNCTION GET_COLUMNS_NAME(p_selectQuery IN VARCHAR2) RETURN cols_name PIPELINED IS v_cursor_id integer; v_col_cnt integer; v_columns dbms_sql.desc_tab; begin v_cursor_id := dbms_sql.open_cursor; dbms_sql.parse(v_cursor_id, p_selectQuery, dbms_sql.native); dbms_sql.describe_columns(v_cursor_id, v_col_cnt, v_columns); for i in 1 .. v_columns.count loop pipe row(v_columns(i).col_name); end loop; dbms_sql.close_cursor(v_cursor_id); return; exception when others then dbms_sql.close_cursor(v_cursor_id); raise; end; / 

Using it:

 select * from TABLE(get_columns_name('select 1 column1, 2 column2 from dual')); 

Results:

 **COLUMN_VALUE** COLUMN1 COLUMN2