windows - How to make Oracle sqlplus spool query results to a file?

Question

Using SQL Plus, I run this script

set colsep ,
set headsep off
set pagesize 0
set trimspool on
set linesize 67
set numwidth 20

spool C:UsersxxxxxAppDataLocalTempncm.txt

SELECT
UNIQ_ID AS UNIQUEID
,REC_ID AS REC_ID
,ACC_NUM AS ACCOUNT
,NOTE_NUM AS NOTENMB
... more columns here
from visn_exp.V_IHCVSN_COMML_LN
WHERE as_of_dt = '30-oct-2020';

spool off

like this:

but when I do, the rows are displayed on the console, not written to the file. What am I missing?

Update: simple test

> sqlplus svc_visn_rrdw@usrrprd

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 27 13:51:47 2021

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed Jan 27 2021 13:26:03 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> spool c:empdual.txt
SQL> select * from dual;

D
-
X

SQL> spool off
SQL>

PS P:
> cat c:empdual.txt
SQL> select * from dual;

D
-
X

SQL> spool off

PS P:
>

When running from cmd instead of Posh, same results as above. query results appear in console (and in target file). I want to stop the results appearing in the console.

question from:https://stackoverflow.com/questions/65925598/how-to-make-oracle-sqlplus-spool-query-results-to-a-file

Answer 1

Use the SQL*Plus command set termout off to disable console output. However, that command only applies to scripts - you must put the commands in a script instead of simply entering all of the commands.

For example, create the file "C:empest.sql" with these contents:

set termout off
spool c:empdual.txt
select * from dual;
spool off

When you run that file, the output will not show on the console but will be spooled into the file.

JHELLER@orclpdb> @C:empest.sql
JHELLER@orclpdb> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
PS C:> cat C:empdual.txt

D
-
X

PS C:>