1

I am using the sql statement listed below in my spool sql script, and I am executing this script from sqlplus. But in terminal query output is still coming and I am using sqlplus in command prompt. But when I tried, directly launching sqlplus.exe and using this script I am not getting query output in terminal.

Can someone please help on how to stop terminal output when I launch sqlplus from command prompt?

SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0  
SET LINESIZE 83
SET TRIMSPOOL ON
SET WRAP ON
SET RECSEP OFF

SPOOL '&1'

SELECT 
    DATA_OUT_DATA 
FROM 
    STAGE.DATA_TABLE
ORDER BY 
    DATA_UNIQ_ID,DATA_INV_ID, DATA_SEQ_NO;

SPOOL OFF

SET PAGESIZE 24
SET LINESIZE 80
SET FEEDBACK ON
SET TERMOUT ON
5

12.41.61 SET TERM[OUT] {ON | OFF}

Controls the display of output generated by commands in a script that is executed with @, @@ or START. OFF suppresses the display so that you can spool output to a file without displaying the output on screen. ON displays the output on screen. TERMOUT OFF does not affect output from commands you enter interactively or redirect to SQL*Plus from the operating system.

Put the above commands in a script, then call that script.

script.sql:

SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0  
SET LINESIZE 83
SET TRIMSPOOL ON
SET WRAP ON
SET RECSEP OFF

SPOOL '&1'

SELECT 
    DATA_OUT_DATA 
FROM 
    STAGE.DATA_TABLE
ORDER BY 
    DATA_UNIQ_ID,DATA_INV_ID, DATA_SEQ_NO;

SPOOL OFF

SET PAGESIZE 24
SET LINESIZE 80
SET FEEDBACK ON
SET TERMOUT ON

Then:

SQL> @script.sql
0

There are three methods to put spool and query script into sql engine:

1, SqlplusWindow

SQL> SPOOL * select query * SPOOL OFF

2, SqlplusWindow

SQL> @spoolquery.sql

3, Shell Terminal or JOB

sqlplus -s "/as sysdba" << EOF_INCLUDE_SCRIPT>/dev/null 2>&1

YourSpool&QueryScriptHere

EOF_INCLUDE_SCRIPT

Here in Method3 the -s hides query script and >/dev/null 2>&1 hides the query result output.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.