Suppress Output in SQL*Plus A friend of mine IM'd me asking if there was a way to supress the output of a query in SQL*Plus. I imagined what he was looking for was similar to what SET AUTOTRACE TRACEONLY does...without all that output.

Off to my local instance.

SYS@TESTING>SELECT * FROM dual;

DUM
---
X

1 row selected.

How about (I swear, this was the first one I tried), SET TERMOUT OFF. That didn't work. (Now guessing) SET ECHO OFF? Nope.

To recap, I want to mimic the behavior of SET AUTOTRACE TRACEONLY where there is no result provided AND I don't want all that other stuff.

SYS@TESTING>SET AUTOTRACE TRACEONLY
SYS@TESTING>/

1 row selected.

Elapsed: 00:00:00.03

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
        418  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

How about The Twitter Machine?

@mgralike, @hillbillytoad, @nlitchfield, @boneist and @dhaimes all jumped in the fray. @hillbillytoad was definitely the most difficult (as usual).

I'll use Storify to tell this story again (so I don't have to screenshot each and every message, in order, and put them in this post).



So the end result?

Create a script, call it test.sql. Put your query in there. From SQL*Plus, issue SET TERMOUT OFF and then run your script. Magic!

SYS@TESTING>SET TERMOUT OFF
SYS@TESTING>@TEST
SYS@TESTING>SET TERMOUT ON
SYS@TESTING>/

         1
----------
         1

1 row selected.

Elapsed: 00:00:00.02

Labels: howto, oradb, sqlplus

  ¶ 5/11/2011 05:58:00 PM