How to Output Oracle SQL Result into a File in Windows

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

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.

How to make Oracle sqlplus spool query results to a file?

Use the SQL*Plus command set termout off to disable console output. However, that command only applies to scripts - you must put the commands in a script instead of simply entering all of the commands.

For example, create the file "C:\temp\test.sql" with these contents:

set termout off
spool c:\temp\dual.txt
select * from dual;
spool off

When you run that file, the output will not show on the console but will be spooled into the file.

JHELLER@orclpdb> @C:\temp\test.sql
JHELLER@orclpdb> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
PS C:\> cat C:\temp\dual.txt

D
-
X

PS C:\>

SQL Oracle - How to save the query output with spool on a text file?

I assume you are using Sql Developer or Toad.
I already answered this one. There is a difference between Run Statement and Run Script. Try Run Script.

Script output to file when using SQL-Developer

Instead of using Run Script (F5), use Run Statement (Ctrl+Enter). Run Statement fetches 50 records at a time and displays them as you scroll through the results...but you can save the entire output to a file by right-clicking over the results and selecting Export Data -> csv/html/etc.

I'm a newbie SQLDeveloper user, so if there is a better way please let me know.

Oracle SQL Developer spool output?

I have found that if I save my query(spool_script_file.sql) and call it using this

@c:\client\queries\spool_script_file.sql as script(F5)

My output now is just the results with out the commands at the top.

I found this solution on the oracle forums.

Oracle SqlPlus - saving output in a file but don't show on screen

Right from the SQL*Plus manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch8.htm#sthref1597

SET TERMOUT

SET TERMOUT OFF suppresses the display so that you can spool output from a script without
seeing it on the screen.

If both spooling to file and writing to terminal are not required, use SET TERMOUT OFF in >SQL scripts to disable terminal output.

SET TERMOUT is not supported in iSQL*Plus

Spool Command: Do not output SQL statement to file

Unfortunately SQL Developer doesn't fully honour the set echo off command that would (appear to) solve this in SQL*Plus.

The only workaround I've found for this is to save what you're doing as a script, e.g. test.sql with:

set echo off
spool c:\test.csv 
select /*csv*/ username, user_id, created from all_users;
spool off;

And then from SQL Developer, only have a call to that script:

@test.sql

And run that as a script (F5).

Saving as a script file shouldn't be much of a hardship anyway for anything other than an ad hoc query; and running that with @ instead of opening the script and running it directly is only a bit of a pain.

---

A bit of searching found the same solution on the SQL Developer forum, and the development team suggest it's intentional behaviour to mimic what SQL*Plus does; you need to run a script with @ there too in order to hide the query text.

---

Related Topics