Re: Extracting ASCII files from database

This is one area where I feel Oracle should have improved. I wish they had something like SQL*Loader to unload data as well. The SQL*Plus spooling method is pretty laborious. If I recall correctly, SYBASE has a nice tool called BCP to load and unload data. May be some one from Oracle will see this message and add it to the wish list.

Murali

brendan_o'brien_at_wrightexpress.com wrote in message <6odngq$c9m$1_at_nnrp1.dejanews.com>...
>I think it's far easier to use sql*plus, provided you're comfortable with
>fixed length output. Using a procedure with DBMS_OUTPUT is bad because it
has
>a limited buffer (100,000 bytes) after which it dies and you get nothing
>(although my understanding is that this has improved in Oracle8).
>
>1. Write your query. Use whatever output manipulation functions you think
>you might need (for example, numeric fields are right justified so you'll
>have to use 'to_char()' on them if you want consistently left justified
>output. Also, you may want to filter your date columns through a format
mask
>other than the default 'dd-MON-yy'). 2. Tack a limiting condition of
'rownum
>< X' onto your WHERE clause and run your query. Use your 'sample' result
set
>to determine all of your starting and ending positions for all fields
(which
>you'll obviously need in your .ctl when you load the file to it's
>destination). 3. Before setting spool on and running the 'real' query (no
>'rownum' in WHERE), make sure to do the following: SET HEADING OFF
(prevents
>output of column headings) SET PAGESIZE XXXXX (where XXXXX is some big
>number no greater than 50000. Prevents unwanted extra newlines except after
>XXXXX rows returned).
>
>You can also do '.csv' style output by having your query concatenate all of
>your columns into a single comma-delimited string with quotes around text.
>The query is laborious to build, however.
>
>Good luck.
>-Brendan
>
>In article <35A939BC.7FA48F00_at_sachsenlb.de>,
> Sigrid Staudte <sstaudte_at_sachsenlb.de> wrote:
>> Hi,
>>
>> I have to extract an ASCII file with fixed length of fields from a
>> database table. This file should be transfered to another server and
>> loaded by the sql*loader. Now I´m searching for the best way to extract
>> the ASCII file.
>> Can I use the spool command and sql*plus or is it necessary to write a
>> pl/sql-programm ?
>> Who can give an advice?
>> Who knows a tool what can help to solve the problem ?
>>
>> Regards,
>>
>> Sigrid
>>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Tue Jul 14 1998 - 00:13:36 CDT