Re: What is the reverse of SQL*Loader?

Little improvement: next settings will always give you a spool file that contains only data and no other lines
set pagesize 0
set feedback off
set verify off
set trimspool on
set lines nnnnn

pagesize: implicitly does set heading off, so no blank lines or headers will apear anymore
trimspool: when off each line is added with spaces to the linesize, when the tool to load can deal with it setting it on can reduce file size tremendously.
linesize: you must now what the maximun length of the retrieved lines is and make it bigger than that, specially when you can't use trimspool on. In 8i SQL*Plus maximum linesize is 32767 (sqlplus 8i will tell you this when you try to set it to a higher value).

If you would like the first line to represent the field names (Excel used that as headers then) include a line:

   prompt xxxxxx
where xxxx is exactly the field names string you want to include (with the field delimeter)

Trifon Anguelov <trifona_at_pacbell.net> schreef in berichtnieuws 2QYB8.2214$mL2.247902324_at_newssvr14.news.prodigy.com...
> Ramon,
>
> Just create a query which extracts an dformats the data you need. For
> example:
>
> SQL> select * from demo;
>
> ID DEMO_TEXT DEMO_DATE
> ---------- ------------------------------ ---------
> 1 TEST 30-APR-02
> 2 TEST 2 29-APR-02
> 3 TEST 3 28-APR-02
> 4 TEST 4 23-APR-02
> 4 MONTH 31-MAR-02
> 4 YEAR 30-APR-01
> 5 YEAR2 30-APR-01
> 6 YEAR3 30-APR-01
> 7 YEAR4 30-APR-01
> 8 MONTH 31-MAR-02
> 9 MONTH 31-MAR-02
> 10 WEEK2 23-APR-02
> 11 MONTH 30-APR-01
> 12 MONTH 30-APR-01
> 13 MONTH 23-APR-02
>
> and then:
>
> SQL> set pagesize 50000
> SQL> set linesize 20000
> SQL> set feedback off
> SQL> spool c:\temp\demo.txt
> SQL>
> SQL> select id||'~'||demo_text||'~'||demo_date
> 2 from demo;
> SQL>spool off
>
>
> ID||'~'||DEMO_TEXT||'~'||DEMO_DATE
> --------------------------------------------------
> 1~TEST~30-APR-02
> 2~TEST 2~29-APR-02
> 3~TEST 3~28-APR-02
> 4~TEST 4~23-APR-02
> 4~MONTH~31-MAR-02
> 4~YEAR~30-APR-01
> 5~YEAR2~30-APR-01
> 6~YEAR3~30-APR-01
> 7~YEAR4~30-APR-01
> 8~MONTH~31-MAR-02
> 9~MONTH~31-MAR-02
> 10~WEEK2~23-APR-02
> 11~MONTH~30-APR-01
> 12~MONTH~30-APR-01
> 13~MONTH~23-APR-02
>
>
> Hope that helps,
>
> Trifon Anguelov
> Senior Oracle DBA
> http://www.dbaclick.com
>
> chack the latest Oracle DBA forums at:
> http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi
>
> --------------------------------------------------------------------------

--


> --------------------------------------------------

>

>

> "Ramon F Herrera" <ramon_at_conexus.net> wrote in message

> news:c9bc36ff.0205071010.34ec682f_at_posting.google.com...

> > - Oracle 8i on Sun SPARC

> >

> > I have been using the 'sqlldr' with success.  Now I need to

> > perform the opposite: to dump a database table into an

> > ASCII file, to export it elsewhere.

> >

> > What options do I have to do this?

> >

> > Thanks,

> >

> > -Ramon F. Herrera

>

>