oracle read column names from select statement

Question

I wonder how read column names in oracle. Ok, I know that there is table named USER_TAB_COLUMNS which gives info about it, but if I have 2 or 3 level nested query and I don't know column names. Or I just have simple query with join statement and i want to get column names. How to do that? any idey?

select * from person a
join person_details b where a.person_id = b.person_id

thanks

May I ask why you don't know the column names? Without knowing details of the schema, including the column names, writing queries will be difficult. — Tim Biegeleisen, Dec 14 '16 at 07:38
I want to write abstract method in java which gets just query and return "assoc map" or something like this. So i don't know what kind of query i 'll pass to it. So PL/Sql developer does this job for us when we pass select statement to it. I need idea how it makes this. — gogagubi, Dec 14 '16 at 07:41
See here: http://stackoverflow.com/questions/8739203/oracle-query-to-fetch-column-names — Tim Biegeleisen, Dec 14 '16 at 07:42
In both Java and .Net you have libraries that take care of this for you. For Java, see this question: http://stackoverflow.com/questions/1194971/dataset-class-in-java. For .Net, look at DataTable, see https://msdn.microsoft.com/en-us/library/system.data.datatable(v=vs.110).aspx. This will work for Oracle, SQL Server, etc. Is this what you are looking for? — GTG, Dec 14 '16 at 08:51

score 0 · Answer 1 · answered Dec 14 '16 at 08:19

I would go for:

select 'select ' || LISTAGG(column_name , ',') within group (order by column_id) || ' from T1' 
  from user_tab_columns 
  where table_name = 'T1';

to get a query from database. To get columns with types to fill map you can use just:

select column_name , data_type
      from user_tab_columns 
      where table_name = 'T1';

Wernfried Domscheit · Answer 2 · 2016-12-14T13:12:46.693

I assume you are looking for this:

DECLARE

    sqlStr VARCHAR2(1000);
    cur INTEGER;
    columnCount INTEGER;
    describeColumns DBMS_SQL.DESC_TAB2;

BEGIN
    sqlStr := 'SELECT a.*, b.*, SYSDATE as "Customized column name" 
              FROM person a JOIN person_details b 
              WHERE a.person_id = b.person_id';

    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS2(cur, columnCount, describeColumns);      
    FOR i IN 1..columnCount LOOP
        DBMS_OUTPUT.PUT_LINE ( describeColumns(i).COL_NAME );
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(cur);

END;

oracle read column names from select statement

2 Answers2

Linked