I have been asked to export some oracle tables to text files. The main
problem I am having is the resulting file.

The sql for the requested fields is stuck at the top of the file and
spool off is at the bottom.

I have used Set ECHO OFF at the top of the command as well as several
other set commands but nothing seems to stop this.

The solution is probably easy but I am not a regular Oracle user so it
is eluding me. If anyone could direct me to a solution, I would
appreciate it greatly.

Thank you.

Try this...
SQL> set pages 0
SQL> set heading off
SQL> set feedback off
SQL> spool yourfilename.txt
SQL> select * from your_table_here;
SQL> spool off
SQL> set feedback on
SQL> set heading on



CriticalJ@gmail.com wrote:
> I have been asked to export some oracle tables to text files. The main
> problem I am having is the resulting file.
>
> The sql for the requested fields is stuck at the top of the file and
> spool off is at the bottom.
>
> I have used Set ECHO OFF at the top of the command as well as several
> other set commands but nothing seems to stop this.
>
> The solution is probably easy but I am not a regular Oracle user so it
> is eluding me. If anyone could direct me to a solution, I would
> appreciate it greatly.
>
> Thank you.

CriticalJ@gmail.com wrote:
> I have been asked to export some oracle tables to text files. The main
> problem I am having is the resulting file.
>
> The sql for the requested fields is stuck at the top of the file and
> spool off is at the bottom.
>
> I have used Set ECHO OFF at the top of the command as well as several
> other set commands but nothing seems to stop this.
>
> The solution is probably easy but I am not a regular Oracle user so it
> is eluding me. If anyone could direct me to a solution, I would
> appreciate it greatly.
>
> Thank you.

I use Tom Kyte's method to make text files:
http://asktom.oracle.com/pls/ask/f?p...ID:68212348056

Thank you for the reply.

I tried your suggestions.

At first I tried them in SQL Plus Worksheet. No matter what
combinations I used, I could not get it to work.

Finally, I tried SQL Plus. After experimenting for a while, I finally
got the data to export to a text file.

As a follow up question, is it possible to get column headers for this
data? Everytime I tried this, it failed miserably. This seems like it
should be easier than what I am making it.

Thanks. I'll look into this as well.

J
cumin wrote:
> CriticalJ@gmail.com wrote:
> > I have been asked to export some oracle tables to text files. The main
> > problem I am having is the resulting file.
> >
> > The sql for the requested fields is stuck at the top of the file and
> > spool off is at the bottom.
> >
> > I have used Set ECHO OFF at the top of the command as well as several
> > other set commands but nothing seems to stop this.
> >
> > The solution is probably easy but I am not a regular Oracle user so it
> > is eluding me. If anyone could direct me to a solution, I would
> > appreciate it greatly.
> >
> > Thank you.
>
> I use Tom Kyte's method to make text files:
> http://asktom.oracle.com/pls/ask/f?p...ID:68212348056

CriticalJ@gmail.com wrote:
> Thank you for the reply.
>
> I tried your suggestions.
>
> At first I tried them in SQL Plus Worksheet. No matter what
> combinations I used, I could not get it to work.
>
> Finally, I tried SQL Plus. After experimenting for a while, I finally
> got the data to export to a text file.
>
> As a follow up question, is it possible to get column headers for this
> data? Everytime I tried this, it failed miserably. This seems like it
> should be easier than what I am making it.

It is easier when you read the sql*plus reference manual.
And obviously
set heading off
will suppress column headers, as that is the very purpose of the
command.
You wouldn't have complained if you would have verified this in the
manual.

--
Sybrand Bakker
Senior Oracle DBA