SQL Plus - SPOOL Command

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

1 - About

SPOOL stores query results in a file, or optionally sends the file to a printer.

Advertising

3 - Syntax

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

4 - Management

4.1 - See

With the spool system variable, you can see the SPOOL information (status and file)

show spool
spool OFF

4.2 - Configuration

trimspool will suppress the spaces.

5 - Usage

One of the first usage of the SPOOL keyword is to create a file with DDL statement through a select statement and run it afterwards.

From SQL Developer, when running the script (F5), you need to run it from an other SqlWorksheet to get the correct output because all SQLPlus command are not implemented

-- Example
@@myScript.sql
SET heading off;
SET feedback off;
SET echo off;
SET lines 9999;
 
Spool compile_invalid.SQL
 
SELECT
   'alter ' || OBJECT_TYPE || ' ' || owner || '.' || object_name || ' compile;'
FROM
   dba_objects
WHERE
   status = 'INVALID'
AND
   object_type IN ('PACKAGE','FUNCTION','PROCEDURE','VIEW')
;
 
spool off;
 
SET heading ON;
SET feedback ON;
SET echo ON;
 
@compile_invalid.SQL
Advertising
lang/sqlplus/spool.txt · Last modified: 2017/09/13 16:16 by gerardnico