CODEWITHSUNDEEP
oracle-database
user458348
user458348

Reputation:

Writing sqlplus output to a file

Using sqlplus.exe I'm looking for a way to write sqlplus output to a file.

Is there anyway I can do that, currently the output is written only to the console.

Upvotes: 76

Views: 263269

Answers (5)

hello_earth
hello_earth

Reputation: 1562

just to save my own deductions from all this is (for saving DBMS_OUTPUT output on the client, using sqlplus):

  • no matter if i use Toad/with polling or sqlplus, for a long running script with occasional dbms_output.put_line commands, i will get the output in the end of the script execution
  • set serveroutput on; and dbms_output.enable(); should be present in the script
  • to save the output SPOOL command was not enough to get the DBMS_OUTPUT lines printed to a file - had to use the usual > windows CMD redirection. the passwords etc. can be given to the empty prompt, after invoking sqlplus. also the "/" directives and the "exit;" command should be put either inside the script, or given interactively as the password above (unless it is specified during the invocation of sqlplus)

Upvotes: 3

Shahriar Khazaei
Shahriar Khazaei

Reputation: 359

In windows type this command:

sqlplus.exe username/password@servicename @yourquery.sql > out.txt

besides, you should write an exit command at the end of "yourquery.sql" file.

Upvotes: 2

Gujju
Gujju

Reputation: 31

Make sure you have the access to the directory you are trying to spool. I tried to spool to root and it did not created the file (e.g c:\test.txt). You can check where you are spooling by issuing spool command.

Upvotes: 3

Ed Gibbs
Ed Gibbs

Reputation: 26333

Also note that the SPOOL output is driven by a few SQLPlus settings:

  • SET LINESIZE nn - maximum line width; if the output is longer it will wrap to display the contents of each result row.

  • SET TRIMSPOOL OFF|ON - if set OFF (the default), every output line will be padded to LINESIZE. If set ON, every output line will be trimmed.

  • SET PAGESIZE nn - number of lines to output for each repetition of the header. If set to zero, no header is output; just the detail.

Those are the biggies, but there are some others to consider if you just want the output without all the SQLPlus chatter.

Upvotes: 42

user2276818
user2276818

Reputation:

You may use the SPOOL command to write the information to a file.

Before executing any command type the following:

SPOOL <output file path>

All commands output following will be written to the output file.

To stop command output writing type

SPOOL OFF

Upvotes: 84

Related Questions