Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Volkenand.

Asked: January 16, 2017 - 8:36 am UTC

Last updated: April 23, 2020 - 7:58 am UTC

Version: 4.0.1.14

Viewed 50K+ times! This question is

You Asked

I would like to know what Statement makes it, that the the SQL Statement is not printet in the spool file?
The column names still should be printed.

I'm calling the script from the SQL developer.

I have the following script: 


set COLSEP ;
--set pagesize 0
set verify off
set feedback off
set trimspool on


spool on


spool h:\Dokumente\Excel-csv-txt\RU-DST_04_2016.txt
--spool W:\SG-323\TeamZentraleKatalogredaktion\+_vk\sql\Sonderlocken-u-RU\Excel-csv-txt\RU-freiz-1-9-ZD-A.xls


select * from ZD_DST 
where (DSTFREI = '1' or DSTFREI = '9') and DSTOG2S like '09%' and DSTDATB > '20161214' order by DSTSCHL;

spool off
--Einstellungen für spool zurücknehmen
set verify on
set feedback on
</code>

and Chris said...

You should:

set term off
set feed off


Note: for this to work, you need to call your statements as a script using @ or @@. Not run them directly.

For example, if I run:

set term off
set feed off
spool c:\temp\temp.txt

select * from dual;

spool off


temp.txt contains:

SQL> select * from dual;

DUMMY  
X      

SQL> spool off


But if I save this as c:\temp.sql and call that:

@c:\temp\temp.sql


The file contains:


DUMMY  
X

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

spool file query oppress

Volkenand Kerstin, January 16, 2017 - 12:58 pm UTC

The answer was useful.

Additional commment:
I would like to know, the oppress is only possible from SQL+ command line ?
The SQl developer I find more comfortable .
Chris Saxon
January 16, 2017 - 1:44 pm UTC

I ran all the statements shown in SQL Developer! So yes, it works there.

Perhaps a different question in the same family?

Nelson, April 22, 2020 - 7:20 pm UTC

What should I do if do not want to spool the result set?

I would like to spool a select statement and get something like:

select * from table where start_date > timestamp'2017-02-12 00:00:00' and start_date < timestamp'2017-08-12 00:00:00';

Elapsed: 00:00:33.90

Execution Plan
----------------------------------------------------------
Plan hash value: 121483743

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |   454K|    23M| 37943   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TABLE           |   454K|    23M| 37943   (1)| 00:00:02 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("START_DATE">TIMESTAMP' 2017-02-12 00:00:00.000000000' AND
              "START_DATE"<TIMESTAMP' 2017-08-12 00:00:00.000000000')


Chris Saxon
April 23, 2020 - 7:58 am UTC

You want to print the execution plan only?

Recent versions of SQL*Plus, SQL Dev, etc. have the set feed only option. This suppresses the rows, but shows the row count.

To get the plan you'll want to call DBMS_Xplan in some way:

set serveroutput off
set echo off
set feed only
set timing on
cl scr
spool c:\temp\out.log
select * from dual;

set feed on
select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
spool off


Spools this to the file:

1 row selected. 

Elapsed: 00:00:00.268
PLAN_TABLE_OUTPUT                    
EXPLAINED SQL STATEMENT:              
------------------------              
select * from dual                    
                                      
Plan hash value: 272002086            
                                      
----------------------------------    
| Id  | Operation         | Name |    
----------------------------------    
|   0 | SELECT STATEMENT  |      |    
|   1 |  TABLE ACCESS FULL| DUAL |    
----------------------------------    
                                      


13 rows selected. 

Elapsed: 00:00:00.414

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.