Skip to Main Content
  • Questions

  • Command to inhibit display of SQL*PLUS results on Windows

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Dianna.

Asked: October 11, 2004 - 10:34 am UTC

Last updated: January 30, 2009 - 12:09 pm UTC

Version: 9.2.0.5

Viewed 50K+ times! This question is

You Asked

Tom,
I know that I can do a "SET TERMOUT OFF" in SQL*PLUS on Unix to inhibit the results of the output going to the display. This doesn't work in Windows, either in the GUI SQL*PLUS [sqlplusw.exe] or the command line SQL*PLUS [sqlplus.exe]. The results are still coming to the screen. Any idea how to do this in Windows?
Thanks,
Dianna

and Tom said...

when placed into a script:


set termout off
stuff
set termout on


it works -- when typed interactively (all platforms) it does not surpress output.

So, were you using in a script?

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I discovered this myself

Dianna, October 11, 2004 - 3:02 pm UTC

After much frustration of reading the documentation and searching Ask Tom, I posted my question. After five minutes after posting my question, I discovered that if executed via a script, it did suppress terminal output. At that time, I had no option to recall the question or post a followup. Thanks Tom, for confirming how it functions. Maybe this will help someone else who needs it.

A reader, October 11, 2004 - 8:32 pm UTC

Interesting, I was also have some issues with other sqlplus commands and now i am just wondering what other commands also have the same behaviour.

Tom Kyte
October 11, 2004 - 9:05 pm UTC

"what issues" exactly do you mean.

termout is documented to do this:

<quote>
Controls the display of output generated by commands executed from a script. OFF suppresses the display so that you can spool output from a script without seeing the output on the screen. ON displays the output. TERMOUT OFF does not affect output from commands you enter interactively.
</quote>

others would be "appinfo", "echo", "flush" and so on (they are documented...)

Beware of HERE documents

George, October 12, 2004 - 3:45 pm UTC

I was caught by "set termout off" when using HERE documents in Unix:
You ran a script, but for sqlplus you are in interactive mode. OK, it's logical, when you think about it.

RE: inhibit the results of output ?

Duke Ganote, April 24, 2008 - 9:23 am UTC 

Perhaps the question was regarding tuning, and SET AUTOTTRACE TRACEONLY was the answer?

whse2000:prot\qtview> set autot traceonly;
whse2000:prot\qtview> select * from dual;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

spool file with the comand I issue and I don't want

Lin, September 03, 2008 - 11:08 am UTC 

I have this script but the log came with the command.
I just want only the results of the command spooled to the file in the "spool" command:

sqlplus /nolog <<EOF
conn /as sysdba
set pagesize 0 heading off feedback off verify off echo off trimspool on
spool test.log
select 'dsmc inc "'||file_name||'">>hot_WISDOM_$BCKNAME.log' from dba_data_files
 where rownum<5;
spool off;
exit
EOF:

And the output is:

SQL> select 'dsmc inc "'||file_name||'">>hot_WISDOM_.log' from dba_data_files where rownum<5;

dsmc inc "/oracle/data1/wisdomut/system01.dbf">>hot_WISDOM_.log
dsmc inc "/oracle/data1/wisdomut/undotbs01.dbf">>hot_WISDOM_.log
dsmc inc "/oracle/data1/wisdomut/WISDOM_DATA1.dbf">>hot_WISDOM_.log
dsmc inc "/oracle/data1/wisdomut/WISDOM_RBS1.dbf">>hot_WISDOM_.log
SQL> spool off;

How can I get rid of the SQL> select 'dsmc inc "'||file_name||'">>hot_WISDOM_.log' from dba_data_files where rownum<5;  line in the spooled file?

Tom Kyte
September 03, 2008 - 12:16 pm UTC

[tkyte@dellpe ~]$ cat test.sh
#!/bin/bash


cat > /tmp/$$.sql <<EOF
set pagesize 0 heading off feedback off verify off echo off trimspool on
spool test.log
select * from dual
/
spool off
exit
EOF

sqlplus / @/tmp/$$.sql

echo ----------------
cat test.log
echo ----------------

rm /tmp/$$.sql

[tkyte@dellpe ~]$ sh test.sh

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Sep 3 11:52:45 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

X
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
----------------
X
----------------



SUGGESTION

stop using sysdba, do not use it, just stop, quite powerful, quite DANGEROUS. Just don't use it - you don't need it and the only thing that could result will be a big bad accident some day.

A reader, September 03, 2008 - 1:08 pm UTC

Is there any way I can suppress even the output of a SQL running from a script and without using "set autotrace traceonly"? I'd like to be able to run some SQLs and get the cost and the logical IO stored in a table. It's be nice to be able to do this without having the output written to the spool file.

Tom Kyte
September 03, 2008 - 4:23 pm UTC

how do you propose to get the logical IO value?

that is, without affecting the logical io.

A reader, September 03, 2008 - 6:14 pm UTC

Couldn't all the processing done in the background without having the output displayed?

Something similar to the following PLSQL code.

DECLARE
   CURSOR c IS
      SELECT * FROM <a table>;

   l_x c%ROWTYPE;

BEGIN
   FOR x IN c LOOP
      l_x := x;
   END LOOP;
END;
/



Tom Kyte
September 04, 2008 - 8:32 am UTC

or just

set arraysize 100
set termout off
select * from t;
set termout on

yes it can, but their stated goal is to capture some output and that would take two sessions to do correctly - to measure the logical IO of a query - you have to run a query. If you query up the logical IO's done by a session, run a query and then query up the logical IO's again, you'll have impacted the logical IO count. Autotrace automagically creates a new session to measure your current session from "outside".

This is why I'm asking how they propose to do this in sqlplus without autotrace output.

How to suppress "Disconnected from ..."

Samuel, January 28, 2009 - 3:04 pm UTC

How do I suppress message like the one below in a spool file from sqlplus? Is there any SET command?

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Thank You,
Tom Kyte
January 30, 2009 - 12:09 pm UTC

spool off

that'll stop spooling.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.