Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Returning column names.

Re: Returning column names.

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Wed, 18 Apr 2001 00:25:03 +0200
Message-ID: <3ADCC2BF.43188608@0800-einwahl.de>

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;

end;
/

print l_cmd
print c_res

undefine tab

spool off

Martin Received on Tue Apr 17 2001 - 17:25:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US