Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 302
Dreamer Avatar asked Mar 06 '13 16:03

Dreamer


People also ask

How do you get the output of SQL query in a Excel file in Oracle?

To start, you'll need to run your query in SQL Developer. ... Step 2: Open the Export Wizard. ... Step 3: Select the Excel format and the location to export your file. ... Step 4: Export the query output to Excel.


2 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.

like image 197
Marc Avatar answered Sep 27 '22 21:09

Marc


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 
like image 26
John D Avatar answered Sep 27 '22 20:09

John D