Search code examples
sqloracle-databaseselecthintspool

Oracle still showing spool statement despite "set serveroutput off"?

I'm running the following commands:

SET PAGESIZE 1000 LINESIZE 500 ECHO OFF TRIMS ON TAB OFF FEEDBACK OFF HEADING OFF SERVEROUTPUT OFF

SPOOL c:/filea1.sql
SELECT /*INSERT*/ * FROM TBL WHERE A = 1;

SPOOL c:/filea2.sql
SELECT /*INSERT*/ * FROM TBL WHERE A = 2;

SPOOL c:/filea3.sql
SELECT /*INSERT*/ * FROM TBL WHERE A = 3;

Every file except the last one has the spool statement at the last line of the file like so -

row1
row2     
SQL> spool C:\next_file_name.sql

I tried putting the set statement between each pair spool and select statement but the result is the same. I also tried breaking the set line into individual statements and putting them all between the spool/select but the result is still the same.

Any suggestions?

Solution

  • Solution is to put code you posted:

    set pagesize ...

...

SELECT /*INSERT*/ * FROM TBL WHERE A = 3;

    into a .sql script (let's call it moshe.sql). Then, at SQL*Plus prompt, run that script:

    SQL> moshe.sql

    It'll make the result look as you expected.

    BTW, don't forget to spool off once you're done (you "forgot" to do that in your code).