Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Returning column names.
Here you have a small script with a testcase that hopefully does what you want:
set echo on
set trimspool on
spool r.sql.lst
drop table TESTTAB;
create table TESTTAB (
n number , c varchar2 (50) , d date
insert into testtab (n, c, d) values (5, 'abc', sysdate); insert into testtab (n, c, d) values (null, 'abc', sysdate); insert into testtab (n, c, d) values (5, '', sysdate); insert into testtab (n, c, d) values (5, 'abc', null);commit;
define tab = TESTTAB
prompt examining table &&tab....
variable c_res refcursor
variable l_cmd varchar2 (250)
declare
l_sel varchar2 (32767):= 'select rowid'; l_cond varchar2 (32767):= 'where 0 = 1'; l_cmd varchar2 (32767); begin for r_col in ( select c.column_name , '"' || c.column_name || '"' as coln from user_tab_columns c where '&&tab' = c.table_name ) loop l_sel:= l_sel
|| ', substr (decode ('
|| r_col.coln
|| ', null, '''
|| r_col.coln
|| ''', null), 1, 30)';
l_cond:= l_cond
|| ' or '
|| r_col.coln
|| ' is null';
end loop; -- for r_col l_cmd:= l_sel || ' from "&&tab" ' || l_cond; :l_cmd:= l_cmd; open :c_res for l_cmd;
print l_cmd
print c_res
undefine tab
spool off
Martin Received on Tue Apr 17 2001 - 17:25:03 CDT