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

In article <32704D3E.7FFB_at_earthlink.net>, Les Gainous <lesgainous_at_earthlink.net> writes
>To all,
>
>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.
>
>Thanks in advance!

Use SQL*Plus.

Although Sybase fans complain about the unfriendliness of the interface compared to ISQL, SQL*Plus has much more functionality. You can write half decent reports in SQL*Plus

For fixed width, just execute the query and spool the output to a file.

eg
spool extract
select x,y,z,a,b,c
from tablea,tableb
where ....
/
spool off

For comma delimited, you have to write a little bit of code using the concatenation operator (||) to string the columns into a single field and rtrim() to remove trailing spaces.

spool extract
select rtrim(x)||','||rtrim(y)||','||rtrim(z)||','||rtrim(a)||','||rtrim (b)||','||rtrim(c)
from tablea,tableb
where ....
/
spool off

There are various formatting commands you will need set linesize n (line width in chars)
set pagesize n (page length in lines)
set heading off (remove column headings from each page) set trimspool on (remove trailing spaces from lines (latest versions only))

Oracle doesn't have a direct equivalent of bcp. For transferring data between Oracle databases in a proprietary format which includings all the DDL to create the database, users, tables as well as the data, use exp and imp.

To load data from flat files ( in fairly complex formats) into Oracle use SQL*Loader.

Rumour has it that in Oracle 8 export/import and SQL*Loader will merge, with 'export format' being an option for SQL*Loader. This implies (although I haven't read it anywhere) that extracting in other formats will be added to SQL*Loader.

Hope this helps

-- 
Jim Smith