Dynamically select the columns to be used in a SELECT statement

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

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;

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.