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?
Add a comment
|
5 Answers
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.
7 Comments
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.
|
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
2 Comments
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
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
Comments
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 :)
Comments
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
1 Comment
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.