33

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?

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Dreamer
  • 7,333
  • 24
  • 99
  • 179

5 Answers5

61

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.

Marc
  • 11,403
  • 2
  • 35
  • 45
  • 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 Mar 06 '13 at 17:02
  • Also how can we specify a path/folder of the output file, especially in Windows? – Dreamer Mar 06 '13 at 17:04
  • 2
    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 Mar 06 '13 at 17:04
  • Could you give an example of the sqlplus.exe version? does it like sqlplus -s username/password@database.domain.com < tmp.sql > path\output.txt? – Dreamer Mar 06 '13 at 17:05
  • 1
    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? – Marc Mar 06 '13 at 17:07
  • You're changing the question now. I guess I'll update my answer. – Marc Mar 06 '13 at 17:09
  • 1
    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. – Dan Pritts Dec 14 '16 at 23:25
21

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
John D
  • 2,307
  • 17
  • 28
  • 1
    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. – Marc Mar 06 '13 at 17:38
  • Thanks John, your version returns clean SQL output I was looked for – Dmytro Zharii Dec 17 '13 at 14:24
4
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

Sindhu
  • 420
  • 1
  • 4
  • 16
2

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 :)

Mohamed Dernoun
  • 776
  • 5
  • 13
2

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

Dudi Boy
  • 4,551
  • 1
  • 15
  • 30
  • 1
    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. – cartbeforehorse Sep 11 '20 at 12:08