How can I get column names from CTE(common table expression) in Oracle?

Question

Is there a function that gives the names a common table expression(~ a subtable in a with statement).

I would like a function f like that

with a as (select 1 a from dual) select f(a.*) from a

column1=a

Or perhaps are the CTE saved somewhere?

I could do something like that.

Is there a equivalent of USER_TAB_COLUMNS for the CTE.

Answer 1

You may use DBMS_SQL package to parse the statement and get the column names:

declare
  cur integer;
  cols dbms_sql.desc_tab;
  num_cols integer;
begin
  cur := dbms_sql.open_cursor();
  /*Prase statement*/
  dbms_sql.parse(
    c => cur,
    statement => q'{
      select dual.*, 'something' as qwe
      from dual
    }',
    language_flag => dbms_sql.native
  );
  /*Get cols*/
  dbms_sql.describe_columns(
    c => cur,
    col_cnt => num_cols,
    desc_t => cols
  );
  
  for i in 1..num_cols loop
    dbms_output.put('Col index: ' || i);
    dbms_output.put_line(' Col name: ' || cols(i).col_name);
  end loop;
  dbms_sql.close_cursor(cur);
end;
/

dbms_output:
Col index: 1 Col name: DUMMY
Col index: 2 Col name: QWE

Or with a local function declaration, if you want it to be selectable:

with function get_cols(
  p_stmt in clob
) return sys.odcivarchar2list
as
  pragma autonomous_transaction;
  ret sys.odcivarchar2list := sys.odcivarchar2list();
  cur integer;
  cols dbms_sql.desc_tab;
  num_cols integer;
begin
  cur := dbms_sql.open_cursor();
  /*Prase statement*/
  dbms_sql.parse(
    c => cur,
    statement => p_stmt,
    language_flag => dbms_sql.native
  );
  /*Get cols*/
  dbms_sql.describe_columns(
    c => cur,
    col_cnt => num_cols,
    desc_t => cols
  );
  
  for i in 1..num_cols loop
    ret.extend();
    ret(i) := cols(i).col_name;
  end loop;
  dbms_sql.close_cursor(cur);
  
  return ret;
end;

select column_value as col_names
from get_cols(p_stmt => q'{select dual.*, 'something' as qwe from dual}')

| COL_NAMES |
| :-------- |
| DUMMY     |
| QWE       |

db<>fiddle here

Answer 2

No. A CTE is part of a DML (data modification language) statement. You wrote it, you should know what columns are in it!

If you need to know the column list of a completed SELECT statement, SELECT ... INTO a temporary table - the column list will then be visible in USER_TAB_COLUMNS.

Answer 3

with a CTE it is not possible. You can write your own function to parse your sql statement. Maybe in your function you can create table with 1=2 condition to get just column names then drop it in your function.

Otherwise;

you can create your table :

create table your_table
as
with a as (select 1 a from dual) select a.* from a where 1=2

then find like this:

SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'your_table'