Re: Extracting ASCII files from database

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 Mon Jul 13 1998 - 14:34:50 CDT