CODEWITHSUNDEEP
sqloracle-database
Everyone_Else
Everyone_Else

Reputation: 3264

How to get column names of a hypothetical table returned by a query oracle sql

Suppose I have a complicated SQL query like

select *
from mydbadmin.tablename
inner join mydbadmin.othertablename
on mydbadmin.tablename.id = mydbadmin.othertablename.id
where mydbadmin.tablename.y = 2

What query can I make to get the headers of the table running this query would return?

Notes:

I've checked Oracle query to fetch column names, but that doesn't seem to help because I'm not trying to find the names from an existing table*.

*I have no ability to actually create this table, so making it and then running the ordinary query isn't an option.

Upvotes: 0

Views: 361

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

You may use the DESCRIBE_COLUMNS Procedure of the DBMS_SQL package

Here are the critical steps: open cursor, parse it and execute. Finaly call the describe_columns, which returns the definition in DBMS_SQL.DESC_TAB

  c := DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE(c, q'[SELECT 'x' col1, sysdate col2, 1 col3 FROM dual]', DBMS_SQL.NATIVE);

  d := DBMS_SQL.EXECUTE(c);

  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

The sample output for the first column of the above query is 

col_type            =    96
col_maxlen          =    1
col_name            =    COL1
col_name_len        =    4
col_schema_name     =    
col_schema_name_len =    0
col_precision       =    0
col_scale           =    0
col_null_ok         =    true

The full example see in Oracle Documentation

As an alternative you may use JDBC resultSet.getMetaData() method.

Upvotes: 2

Related Questions