Re: Building dynamic sql that will do CSV

see http://www.cybcon.com/~jkstill/util/dump/dump.html

On Thu, 2003-08-28 at 08:49, Michael Kline wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.com

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).