Spool Command Doesnt Save Result in Query

SPOOL command doesnt save result in query

Try to execute it with a query which returns fewer rows to see if you have any other problems. After you make it work, try your query.
Also there is a difference between Run Statement and Run Script.

In the following query

spool '/home/atilla/file.txt'

SELECT * FROM DUAL;

SPOOL OFF

If I use Run Statement, I get following file

   \> SELECT * FROM DUAL

If I use Run Script, I get following file

\> SELECT * FROM DUAL
DUMMY
-----
X   

Spool not saving the result of a query

This was the expected behaviour in version 4.1.5 and earlier (see this, for example).

I believe the behaviour changed in version 4.2.0, and in the current version 18.2 it certainly works as you would like; so really you need to upgrade to avoid this issue. You can download it here.

You also need to add

set heading off

to the start of your script, which will remove the header line (which will cause its own errors if present); or in later versions you can do:

set pages 0

instead (or as well), which will remove the header line and more blank lines.

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.

Using spool in Oracle 11g doesn't send the result of SELECT queries to the file

Please make sure you run your statements as a script (using the Run Script F5) button and not as a regular command execution.

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.

---

Related Topics