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

Home -> Community -> Mailing Lists -> Oracle-L -> Building dynamic sql that will do CSV

Building dynamic sql that will do CSV

From: Michael Kline <mkline1_at_comcast.net>
Date: Thu, 28 Aug 2003 07:49:45 -0800
Message-ID: <F001.005CDBF8.20030828074945@fatcity.com>


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','INVEN
TORY')
        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.
Principal Consultant
Business to Business Solutions, LLC
Phone: 804-744-1545 Cell: 804-314-6262
ICQ: 1009605, 975313
Email: mkline_at_b2bsol.com Web: www.b2bsol.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Kline
  INET: mkline1_at_comcast.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Aug 28 2003 - 10:49:45 CDT

Original text of this message

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