Dynamic Detection Of Column Names In PL/SQL

The sys package dbms_sql can be used to get the column names from a SQL query statement.

This is shown in the PL/SQL block below where the column names are detected for a query and

printed using dbms_output.

declare

l_col_count number;

l_col_descs dbms_sql.desc_tab;

l_cursor number;

l_statement varchar2(50);

l_table_name varchar2(50);

begin

-- Define the table name

l_table_name := 'scott.bonus';

--

-- Build the SQL statement

l_statement := 'select * from ' || l_table_name;

--

-- Open a new cursor

l_cursor := dbms_sql.open_cursor;

--

-- Parse the SQL statement

dbms_sql.parse

( c => l_cursor

, statement => l_statement

, language_flag => dbms_sql.native

);

--

-- Describe the columns of the cursor

dbms_sql.describe_columns

( c => l_cursor

, col_cnt => l_col_count

, desc_t => l_col_descs

);

--

-- Output the results: Header

dbms_output.put_line

( 'Description of table ' ||

l_table_name ||

':'

) ;

--

-- Output the results: Column names

for i in 1..l_col_count

loop

dbms_output.put_line

( 'column ' ||

to_char(i) ||

': name=' ||

l_col_descs(i).col_name

);

end loop;

end;

--Output:

Description of table scott.bonus:

column 1: name=ENAME

column 2: name=JOB

column 3: name=SAL

column 4: name=COMM

/