-
suppress display
Hello, Can somebody please tell how to suppress the query result in sql*plus? I need something to work like unix re-direction operator. i.e I want to issue a select statement and send the results (rows) to a file. They should NOT be displayed on screen. I am using spool to do so, but need help with suppressing the results.
I used the following
set verify off
set heading off
set linesize 6000
set feedback off
set termout off
set term off
spool out1.txt
I used sqlplus -s option. It suppressed the screen display but not the query result. The records displayed as usual. Is there anything else that I should add to the above? Thanks again.
-
TERMOUT is the answer.
From the doc:
SET TERM[OUT] {ON|OFF}
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.
-
I used this sequence of commands :
set verify off;
set term off; //also with set termout off tried both seperately
set linesize 6000;
set feedback off;
set pagesize 0;
set echo off;
set sqlprompt '';
set trimspool on;
set termout off;
spool out_C1.txt
It STILL displays the rows ... I don't want sql*plus scroll 1/2 million records. Basically, I want to spool the records to a text file ... Is this the right way?
Thanks
-
What's the problem?
Code:
[marist89@jake marist89]$ more noout.sql
set termout off
set echo off
spool foo.out
select * from t1;
spool off
quit
[marist89@jake marist89]$ sqlplus -s a/a@lx920.us @noout.sql
[marist89@jake marist89]$ more foo.out
F1 F2
---------- --------------------
1 A
2 B
Elapsed: 00:00:00.01
[marist89@jake marist89]$
-
TERMOUT needs to be in the script to work.
Here is a script called yo_momma.sql ....
set termout off;
spool d:\your_momma.txt;
select * from emp;
spool off;
SQL> @yo_momma.sql
SQL>
It creates a .txt file with all the rows in it, test it and see.
MH
-
Thanks for giving "your_momma.txt"
-
Actually I am wrong.
SET TERMOUT will suppress the display on a called script.
SQL> @D:\yo_momma
So make sure you turn TERMOUT back on at the end of your script.
MH
-
Thanks for giving "your_momma.txt"
-
Sorry, that was a browser problem ... I tried Marist's script and it worked well. Mr.Hanky your script seems to be the same. Thanks for the tip. BUT, please note that if I type the commands on plain sql*plus window and spool, it still displays output. I don't know why TERMOUT is not working here.
Thanks both.
-
Once again, from the Doc:
"TERMOUT OFF does not affect output from commands you enter interactively"
You are right...it WILL not work, because that is how Oracle has designed it !