Oracle SqlPlus - saving output in a file but don't show on screen

Question

Using SqlPlus for Oracle, how do I save the output of a query in a file but not show it on the terminal/prompt.

SET TERMOUT OFF is not working wiht my SqlPlus. This sqlplus `-s ${ORA_CONN_STR} </dev/null` i.e. answer by @AshishAnand worked for me. — Sachin, Jan 13 '15 at 11:11
"SET TERMOUT is the way to do it and it requires a script - it doesn't do anything at an interactive prompt" - https://community.oracle.com/thread/2140535?tstart=0 — Straff, Jan 12 '16 at 23:30

score 53 · Accepted Answer · edited Jan 11 '19 at 09:22

53

Right from the SQL*Plus manual
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch8.htm#sthref1597

SET TERMOUT

SET TERMOUT OFF suppresses the display so that you can spool output from a script without seeing it on the screen.

If both spooling to file and writing to terminal are not required, use SET TERMOUT OFF in >SQL scripts to disable terminal output.

SET TERMOUT is not supported in iSQL*Plus

edited Jan 11 '19 at 09:22

Martin Ba

37,187
33
183
337

answered Jul 25 '11 at 08:20

7

"SET TERMOUT is the way to do it and it requires a script - it doesn't do anything at an interactive prompt" - community.oracle.com/thread/2140535?tstart=0 – Straff Jan 12 '16 at 23:31

score 21 · Answer 2 · edited Mar 01 '12 at 09:14

21

Try this:

SET TERMOUT OFF; 
spool M:\Documents\test;
select * from employees;
/
spool off;

edited Mar 01 '12 at 09:14

Zsolt Botykai

50,406
14
85
110

answered Mar 01 '12 at 04:52

Mohammad

211
2
2

@AlexPoole the spool command. IMO this is a more complete answer. – frenchmd Dec 12 '12 at 14:38
[be careful using semicolon `;` and slash `/` both, as that will duplicate your query result](https://stackoverflow.com/q/1079949/4023950) – αғsнιη Oct 15 '18 at 12:35

score 11 · Answer 3 · answered Mar 02 '13 at 06:37

11

Try This:

sqlplus -s ${ORA_CONN_STR} <<EOF >/dev/null

answered Mar 02 '13 at 06:37

Ashish Anand

3,531
6
34
45

On UNIX it just hangs on a prompt prefixed by ">" – Wassadamo Apr 08 '20 at 00:20
`source .env; ORA_CONN="${USER}/${PASS}@{DB}"; sqlplus -s </dev/null $ORA_CONN select 1 from dual; EOF` worked for me – Wassadamo Apr 08 '20 at 00:56

score 3 · Answer 4 · answered Jun 21 '18 at 14:28

3

set termout off doesn't work from the command line, so create a file e.g. termout_off.sql containing the line:

set termout off

and call this from the SQL prompt:

SQL> @termout_off

answered Jun 21 '18 at 14:28

pelms

1,212
1
12
21

Oracle SqlPlus - saving output in a file but don't show on screen

4 Answers4

Linked