set colsep ','
might help too.
Raj
Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this email
are strictly personal. QOTD: Any clod
can have facts, having an opinion is an art !
-Original Message-From: Michael Kline
[mailto:[EMAIL PROTECTED]Sent: Thursday, August 28, 2003 11:50
AMTo: Multiple recipients of list ORACLE-LSubject:
Building dynamic sql that will do CSV
I'm trying to
build something like this:
select 'select
'''||TABLE_NAME||','||COLUMN_NAME||''', ''',''',
min('||COLUMN_NAME||'),max('||COLUMN_NAME||') from
' ||OWNER||'.'||TABLE_NAME||'
where '||COLUMN_NAME||' is not
null;' FROM
dba_tab_columns WHERE table_name
in
('PAYMENTS_RECEIVED','ADDRESS_VAL','BATCH_CONTROL',
'CUSTOMER','CUSTOMER_DEFAULT','EXPLODED_PRODUCT','EXPLODED_PUBLICATION',
'EXPLOSION','ORDERS_MERCH','INVOICE','INVOICE_MERCH','CLIENT_SAMPLES','INVENTORY')
AND OWNER = 'HIST'order by owner, table_name,
column_id;
which gives me
select
'CLIENT_SAMPLES,SAMPLE_DATE5', '',
min(SAMPLE_DATE5),
max(SAMPLE_DATE5) from HIST.CLIENT_SAMPLES where SAMPLE_DATE5 is not
null;
and an output
of:
CLIENT_SAMPLES,SAMPLE_DATE5
0
11/09/2002 09:38
What I want is a
"comma" between everything and perhaps ", but building this dynamically has
got my brain all fuddled. Trying to figure out what to build in the dynamic to
get the executed sql to look right... I just can't seem to get the right
combination...
I'd like
something like:
CLIENT_SAMPLES,SAMPLE_DATE5,
0
,11/09/2002 09:38
or
"CLIENT_SAMPLES","SAMPLE_DATE5","0
","11/09/2002 09:38"
Michael Alan Kline, Sr.PrincipalConsultantBusiness to Business
Solutions, LLCPhone: 804-744-1545 Cell: 804-314-6262ICQ:
1009605, 975313Email: [EMAIL PROTECTED] Web: www.b2bsol.com
This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*2