Spool not saving the result of a query

Question

I'm trying to save a result of a query in a text file, but my spool is not saving the query result, but the query itself. Looking on Stack Overflow I saw a workaround - save a SQL script file and run that script, but it is not working too.

set verify off
set echo off
spool  C:\PSR\output.sql

select id_issue
from 
mo_test
where 
rownum<10;

spool off

@C:\PSR\output.sql

Error starting at line : 1 File @ C:\PSR\output.sql
In command -
SQL> select id_issue
Error report -
Unknown Command


Error starting at line : 2 File @ C:\PSR\output.sql
In command -
from 
Error report -
Unknown Command


Error starting at line : 3 File @ C:\PSR\output.sql
In command -
mo_test
Error report -
Unknown Command

...

Software Version from 'About SQL Developer':

Oracle IDE 4.1.5.21.78
JDK 1.8.0_102

your version of SQL Developer is pretty old (4.1.5) but I'm not aware of that bug ever being around. I can confirm that modern versions can easily do a spool ON...query...spool OFF and work just fine. You can also just run the query with f9 and right-click on the grid and do a 'Export' to get the results to a file — thatjeffsmith, Aug 31 '18 at 18:14
can you show exactly what's in the file? Also, try to upgrade and i think you'll be fine — thatjeffsmith, Aug 31 '18 at 18:19
@thatjeffsmith - I think it's the same issue as [this forum discussion](https://community.oracle.com/message/10387562), and it changed in 4.2.0 according to a comment I added [on this answer](https://stackoverflow.com/a/19034156/266304). It didn't seem to be a bug as such as SQL\*Plus does the same without the `-s` flag, I think. — Alex Poole, Aug 31 '18 at 18:22
Yes, i saw that, for me i need to export the result but i'll do this download a new version and try this code again to see if echo off will work. — Lessandro, Aug 31 '18 at 18:33
Right... I kind of assumed that was a language issue, and it's saving the query as well as the results. Which is what I see. Running exactly what's shown in 4.1.5 (after creating a dummy table) gets that error, but as the third in a series of at least six. Those errors are coming from the original statement being spooled. It does contain the actual query results afterwards though, and tries to run that. — Alex Poole, Aug 31 '18 at 18:35
@Lessandro - I've added the errors I see before the one you originally reported; can you confirm you see the same (plus others), and that your `output.sql` file contains the query *and* its results, rather than *only* the query? (It's a moot point if you're upgrading anyway, but it would still be interesting!) — Alex Poole, Aug 31 '18 at 18:43
Hi, @AlexPoole i upgrate to 18.2.0.183.1748, but when i run the query my output.sql get inside only my quer, in that case "select id_issue from mo_test where rownum<10;" — Lessandro, Sep 03 '18 at 13:54
In 18.2.0.183.1748, with `set echo off` I only get the output, not the query; with `set echo on` I get both. I don't see how you're only getting the query, unless there is no data, but then you would still see 'no rows selected' with those settings. Are you sure it is actually overwriting that file? Seems to be one for @thatjeffsmith after all... — Alex Poole, Sep 03 '18 at 14:08
@AlexPoole i've never seen headers no data in terms out output/spool, of course not sure what kind of data is in id_issue.mo_test — thatjeffsmith, Sep 03 '18 at 14:22
@AlexPoole only numbers from 1 to 100 in my id_issue, maybe the problem could be my OS, im using windows,not linux — Lessandro, Sep 04 '18 at 13:08

Alex Poole · Accepted Answer · 2018-08-31T18:29:07.203

0

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.

edited Aug 31 '18 at 18:29

answered Aug 31 '18 at 18:04

Alex Poole

183,384
11
179
318

This error with set pages 0 => SP2-0267: pagesize option 0 out of range (2 through 50000) – Lessandro Aug 31 '18 at 18:26
Ha, that's changed in later versions too. Then set it high, or leave it as its default; and `set heading off` instead. You have to upgrade to avoid the original issue anyway though, and you can use either in version 18.2. – Alex Poole Aug 31 '18 at 18:27

Spool not saving the result of a query

1 Answers1