Re: Smart Way to Extract from Oracle to a File

Jackie Keller wrote:

> What is the smartest or best way of selecting data from the oracle tables
> and writing it to a file? We have done similar things (not w/Oracle) using
> report writers. However, it appears that Oracle Reports will not let me do
> this. Do I need to use PL/SQL and output to a file???

Nope. Use sqlplus with an SQL script like this:

select .... from ...

spool <output file>
/
spool off
exit

Save this in a file, eg. script.sql.

The meaning of this is: first you define you select statement and load it into

SQLplus' buffer. Then you use the SQLplus command spool to capture any output in a file - replace <output file> with a proper file name. The '/' executes the select statement in the buffer without listing the buffer like
the run command does. 'Spool off' stops spooling and exit exits :-)

You can execute this interactively by starting SQLplus and issuing the command:

@script

from the SQLplus command line, assuming that you saved your commands in the file script.sql. You can make it batch-wise like this:

sqlplus <user>/<password>@<connect string> @script

where <user> is the Oracle user name, <password> is the password and <@connect string> is the 'host string' that you use to connect to a remote database - you can omit the @<connect string> if it is local. There must of course be a space before @script. If you are working in UNIX, you can use a 'here document' instead of a file - if your login shell is bash, it would look like:

sqlplus <user>/<passwd>@<host> <<!
select ....

spool <file>
/
spool off
exit
!

This is very useful in a shell script.

/jan Received on Thu Oct 01 1998 - 03:29:07 CDT