Wednesday, May 11, 2011

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

2 comments:

hillbillyToad said...

Hmmm, I realized I'm used to running sqlplus scripts via Toad. When doing so, it auto 'runs as script'. So toadily my bad for not being clear about running as script a la @ vs interactive.

You're going to have people talking that we don't like each other, when in reality we really hate each other.

oraclenerd said...

Yeah, but it's fun at least. We haven't hurt any small animals in our flame wars...yet.

"toadily" - good one.