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

Home -> Community -> Usenet -> c.d.o.misc -> Re: -> Write table data to a text file? How? <-

Re: -> Write table data to a text file? How? <-

From: Jim Smith <jim_at_jimsmith.demon.co.uk>
Date: 1996/11/04
Message-ID: <4JpnMDAgWcfyEwOQ@jimsmith.demon.co.uk>#1/1

In article <327D860D.24AE_at_decspop.nexus.edu.au>, Grant Sinclair <gsinclai_at_decspop.nexus.edu.au> writes
>Halina Monka wrote:
>>
>> Les Gainous wrote:
>> >
>> > What's the best way to write data out to a text file: comma-delimited,
>> > tab-separated, or fixed length? I basically need to write out a table
>> > into a format usable by other RDMSs/applications. I'm looking for
>> > something similar to the Bulk-Copy Program from Sybase or Microsoft SQL
>> > Server.

 (snip)
>> The common way to dump Oracle table into the text file is to use
>> spool command in sqlplus. All sqlplus formatting features may be
>> aplied to achieve required format.
>> e.g
>> spool myfile.txt # this will create operating system file
>> # and all output up to sppol off will be captured
>> select col1,.....
>> from table(s)
>> where
>> order....
>> spool off
>>
>> Halina Monka
>
>In case not obvious, for fixed length, suppressing spaces between
>columns
>is a good idea. For comma delimited, you can have a select like this:
> select rtrim(col1) || ',' || rtrim(col2) || ',' ...
>
>Various SET options (e.g. PAGESIZE 0, HEADING OFF, LINESIZE) are also
>important; read up on these. For big files, putting everything in a
>script and executing with SET TERMOUT OFF is also a must, or it takes
>ages.
>
>I'd like to make SQL*Plus put the commas in between for me
>automatically,
>but I'm yet to find a SET option that will do this.

In SQL*Plus 3.3 (with 7.3) there is now a SET COLSEP option which does exactly that. Now all that is needed is an automatic rtrim option!

-- 
Jim Smith
Received on Mon Nov 04 1996 - 00:00:00 CST

Original text of this message

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