How to output oracle sql result into a file in windows?

Question

I tried

select * from users 
save D:\test.sql create;

But SQL plus gives me "no proper ended" How to specify path in oracle sql in windows?

Answer 1

Use the spool:

spool myoutputfile.txt
select * from users;
spool off;

Note that this will create myoutputfile.txt in the directory from which you ran SQL*Plus.

If you need to run this from a SQL file (e.g., "tmp.sql") when SQLPlus starts up and output to a file named "output.txt":

tmp.sql:

select * from users;

Command:

sqlplus -s username/password@sid @tmp.sql > output.txt

Mind you, I don't have an Oracle instance in front of me right now, so you might need to do some of your own work to debug what I've written from memory.

• Dreamer: thank you but can I ask where is the spool file myoutputfile.txt locate? Also what about user don't have permission to run spool?
• Dreamer: Also how can we specify a path/folder of the output file, especially in Windows?
• Marc: I've never had luck specifying where the spool is written to except to run sqlplus.exe from the directory where the spool output will go.
• Marc: I don't understand your question. From SQL*plus, type exactly what I wrote in my solution. You will get output in a file named myoutputfile.txt. If you don't see it where you expected it, perform a search for it. How are you executing SQLPlus? From what directory?
• Dan Pritts: Note that if you do not specify a file extension on the spool file name (like .txt in the example), the extension .lst will be automatically appended.

Answer 2

Very similar to Marc, only difference I would make would be to spool to a parameter like so:

WHENEVER SQLERROR EXIT 1
SET LINES 32000
SET TERMOUT OFF ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
SET SERVEROUTPUT ON
spool &1
-- Code
spool off
exit

And then to call the SQLPLUS as

sqlplus -s username/password@sid @tmp.sql /tmp/output.txt

• Marc: Yep. I was gonna add the set pages 0, set trimspool on, etc. But then the question changed and I had to chase down something else! Good suggestions though.
• Dmytro Zharii: Thanks John, your version returns clean SQL output I was looked for

Answer 3

spool "D:\test\test.txt"
select 
 a.ename 
from 
 employee a 
inner join department b 
on 
( 
 a.dept_id = b.dept_id 
) 
; 
spool off 

This query will spool the sql result in D:\test\test.txt

Answer 4

just to make the Answer 2 much easier, you can also define the folder where you can put your saved file

 spool /home/admin/myoutputfile.txt
 select * from table_name;
 spool off;

after that only with nano or vi myoutputfile.txt, you will see all the sql track.

hope is that help :)

Answer 5

Having the same chore on windows 10, and windows server 2012. I found the following solution:

echo quit |sqlplus schemaName/schemaPassword@sid @plsqlScript.sql > outputFile.log

Explanation

echo quit | send the quit command to exit sqlplus after the script completes

sqlplus schemaName/schemaPassword@sid @plsqlScript.sql execute plssql script plsqlScript.sql in schema schemaName with password schemaPassword connecting to SID sid

> outputFile.log redirect sqlplus output to log file outputFile.log

• cartbeforehorse: It's better to spool inside the .sql file I think. Redirecting standard output to file may result in unwanted SQL feedback output in your file. Also, you could just quit (or exit) inside the .sql file.