SQL Plus - SPOOL Command

1 - About

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

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

Set heading off;
set feedback off;
set echo off;
Set lines 9999;

Spool compile_invalid.sql

   'alter ' || OBJECT_TYPE || ' ' || owner || '.' || object_name || ' compile;'
   status = 'INVALID'
   object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW')

spool off;

set heading on;
set feedback on;
set echo on;


Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap