CODEWITHSUNDEEP
sqlplsqloracle11g
Spoons
Spoons

Reputation: 75

Return only the column headers as text

I'm trying to return the value of a set of column headers as text, similar if you will to using the listagg function, however, these fields can be user defined and not necessarily located in a table, so using lisatagg on dba_tab_cols isn't an option.

An example piece of code I'm working with is the following

with test_data1 as (select '2012' extract_yr, '01' extract_mth, 'John Smith' person1, 'Jane Doe' person2 from dual)
    ,test_data2 as (select '2016' extract_yr, '01' extract_mth, 'John Smith' person1, 'Jane Doe' person2 from dual)

select t1.extract_yr year
      ,t1.extract_mth month
      ,t1.person1 first_person
      ,t1.person2 second_person
from test_data1 t1
union all
select t2.extract_yr year
      ,t2.extract_mth month
      ,t2.person1 first_person
      ,t2.person2 second_person
from test_data2 t2

What I'm after is getting the header into a comma delimited output/variable, so I can then use them in some dynamic SQL a little further on down the line. So in the example above, I would expect to see:

YEAR, MONTH, FIRST_PERSON, SECOND_PERSON

This is for a script automation programme that I'm writing, so the output needs to be into a clob as comma delimited. Hundreds of scripts will be executed and the output fields will differ from script to script.

Any help or pointers in the right direction would be much appreciated

Upvotes: 1

Views: 204

Answers (3)

Andrey Khmelev
Andrey Khmelev

Reputation: 1161

second way is to use dbms_sql.describe_columns

declare
    v_sql varchar2(32767) := 'with test_data1 as (select ''2012'' extract_yr, ''01'' extract_mth, ''John Smith'' person1, ''Jane Doe'' person2 from dual)
                                    ,test_data2 as (select ''2016'' extract_yr, ''01'' extract_mth, ''John Smith'' person1, ''Jane Doe'' person2 from dual)
                                select t1.extract_yr year
                                      ,t1.extract_mth month
                                      ,t1.person1 first_person
                                      ,t1.person2 second_person
                                from test_data1 t1
                                union all
                                select t2.extract_yr year
                                      ,t2.extract_mth month
                                      ,t2.person1 first_person
                                      ,t2.person2 second_person
                                from test_data2 t2';
    v_cursor_id integer;
    v_col_cnt integer;
    v_columns dbms_sql.desc_tab;
    v_cols varchar2(250);
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);

        if i < v_columns.count then
            v_cols := v_cols || v_columns(i).col_name || ', ';
        else
            v_cols := v_cols || v_columns(i).col_name;
        end if;

    end loop;

    dbms_sql.close_cursor(v_cursor_id);
    dbms_output.put_line(v_cols);
exception when others then
    dbms_sql.close_cursor(v_cursor_id);
    raise;
end;

Upvotes: 1

Andrey Khmelev
Andrey Khmelev

Reputation: 1161

I am not sure, but maybe it makes sense for you.

First we create new temporary table

create GLOBAL TEMPORARY table mytmp
    ON COMMIT DELETE ROWS
    as with test_data1 as (select '2012' extract_yr, '01' extract_mth, 'John Smith' person1, 'Jane Doe' person2 from dual)
        ,test_data2 as (select '2016' extract_yr, '01' extract_mth, 'John Smith' person1, 'Jane Doe' person2 from dual)
    select t1.extract_yr year
          ,t1.extract_mth month
          ,t1.person1 first_person
          ,t1.person2 second_person
    from test_data1 t1
    union all
    select t2.extract_yr year
          ,t2.extract_mth month
          ,t2.person1 first_person
          ,t2.person2 second_person
    from test_data2 t2;

then exctract our columns

SELECT LISTAGG(Column_Name, ', ') WITHIN GROUP (ORDER BY Column_Name)
    FROM   all_tab_cols
    WHERE  table_name = upper('mytmp');

and drop table

drop table mytmp;

Upvotes: 0

sagi
sagi

Reputation: 40481

Do you mean something like this?

SELECT LISTAGG(Column_Name, ', ')
       WITHIN GROUP (ORDER BY GROUP) as ColumnNames
FROM   all_tab_cols
WHERE  table_name = 'TableName'
       AND owner = 'OwnerName'

If you want it in addition to your results, just join them.

Upvotes: 0

Related Questions