Dynamically select the columns to be used in a SEL

Question

I would love to be able to use the system tables (Oracle in this case) to drive which fields are used in a SELECT statement. Something like:

SELECT 
(
select  column_name
from    all_tab_cols
where   table_Name='CLARITY_SER'
AND     OWNER='CLARITY'
AND     data_type='DATE'
) 
FROM CLARITY_SER

This syntax doesn't work, as the subquery returns multiple rows, instead of one row with multiple columns.

Is it possible to generate a SQL statement dynamically by querying the table schema information in order to select only certain columns?

** edit ** Do this without using a function or procedure, if possible.

Answer 1

In SQLPlus you could do this:

COLUMN cols NEW_VALUE cols

SELECT max( ltrim( sys_connect_by_path( column_name, ',' ), ',' ) )  cols
FROM
(
select rownum rn, column_name
from    all_tab_cols
where   table_Name='CLARITY_SER'
and     OWNER='CLARITY'
AND      data_type='DATE'
)
start with rn = 1 connect by rn = prior rn +1
;

select &cols from clarity.clarity_ser;

Answer 2

No, it's not possible to specify a column list dynamically in SQL. You'll need to use a procedural language to run the first query, use that to construct a second query, then run the second query.

Answer 3

You could use dynamic SQL. Create a function that takes the table name, owner, data type, executes the inner query and returns a comma-separated list of column names, or an array table if you prefer. Then construct the outer query and execute it with execute immediate.

CREATE FUNCTION get_column_list(
      table_name IN varchar2,
      owner_name IN varchar2,
      data_type  IN varchar2) 
   RETURN varchar2 
   IS 
BEGIN 
...... (get columns and return comma-separated list)
 END;
/

If your function returns a comma-separated list you can inline it:

execute immediate 'select ' || get_column_list(table_name, owner_name, datatype) || ' from ' || table_name

Admittedly it's a long time since I played with oracle so I may be a bit off but I'm pretty sure this is quite doable.

Answer 4

You can do this:

declare
  l_sql varchar2(32767);
  rc sys_refcursor;
begin
  l_sql := 'select ';
  for r in
  ( select  column_name
    from    all_tab_cols
    where   table_Name='CLARITY_SER'
    AND     OWNER='CLARITY'
    AND     data_type='DATE'
  )
  loop
    l_sql := l_sql || r.column_name || ',';
  end loop;
  l_sql := rtrim(l_sql,',') || ' from clarity_ser';
  open rc for l_sql;
  ...
end;