2.8. Script Output

You redirect SQL*Plus output with a SPOOL command. You choose to “spool to a file” at the beginning of the script. You “end the spooling process” at the end of the script. Some scripts can be lengthy—they drop and recreate 50 tables and include many insert statements for test data. These types of scripts often spool output for verification purposes.

					-- filename MY_STUDENTS.sql
spool my_students
DROP TABLE students;
CREATE TABLE students
(student_id    VARCHAR2(10),
 student_name  VARCHAR2(30),
 college_major VARCHAR2(15),
 status        VARCHAR2(20) ) TABLESPACE student_data;

INSERT INTO students VALUES
 ('A123','John','Math','Degree'),

INSERT INTO students VALUES
 ('A124','Mary','Biology','Degree'),
COMMIT;
spool off

The output from the spool file is named MY_STUDENTS.LST. It contains only the feedback from SQL*Plus. The text of the spool output file is brief and primarily shows that the statements completed successfully. Listing of MY_STUDENTS.LST gives the following five lines:

Table dropped.
Table created.
1 row created.
1 row created.
Commit complete.

By default, the spool file shows only the success or failure of the SQL statement submitted. You have several options if you want the spool file to include the SQL statement. One option is to include the SQL*Plus command SET ECHO ON at the beginning of your SQL script, and SET ECHO OFF at the end of the script. This option is the most practical because you can easily comment/uncomment these ECHO commands. Another option is to embed LIST SQL*Plus commands within your script.

The default extension for a SPOOL file is “LST” on Windows and UNIX, but “LIS” on OpenVMS. You can specify the extension of a spool file (e.g., you might want to actually spool text where the spooled text is a series of SQL statements, then run the output as a SQL script). This spool command would specify a SQL file extension.

SPOOL filename.SQL

The following script satisfies a common request: generate the table counts for all tables. The following is a SQL script that spools output consisting of SQL statements.

					-- filename GEN_COUNTS.sql
spool GEN_COUNTS_OUT.SQL
SELECT 'select count (*) from '||table_name||';'
FROM    user_tables;
spool off

The output is a script of SQL statements. However, the output, which is the file GEN_COUNTS_OUT.SQL, contains some unwanted text (specifically, a heading and the feedback showing the number of rows selected). The resulting spool file output is:

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
-----------------------------------------------------
select count (*) from STUDENTS;

1 row selected.

Eliminate the heading information and SQL*Plus feedback with SET TERM OFF and SET FEEDBACK OFF. The revised script is:

					-- filename GEN_COUNTS.sql
set heading off
set pagesize 1000
set term off
set feedback off
spool GEN_COUNTS_OUT.SQL
SELECT 'select count (*) from '||table_name||';'
FROM    user_tables;
spool off
set heading on
set feedback on
set term on

The GEN_COUNTS.SQL script now generates just a SQL script. The following would be the contents of GEN_COUNTS_OUT.SQL

select count (*) from STUDENTS;
select count (*) from PROFESSORS;

The output script is GEN_COUNTS_OUT.SQL, which will perform row counts for each table. We can run GEN_COUNTS_OUT and get these numbers but the output will only show row counts. The table name is needed as well as the row count. The SQL generation script, GEN_COUNTS.SQL, undergoes a modification. The following script now includes a SQL SELECT FROM DUAL statement that will spool SET ECHO commands.

					-- filename GEN_COUNTS.sql
set heading off
set pagesize 1000
set term off
set feedback off
spool GEN_COUNTS_OUT.SQL
SELECT 'set echo on' from dual;
SELECT 'select count (*) from '||table_name||';'
FROM   user_tables;
SELECT 'set echo off' from dual;
spool off
set heading on
set feedback on
set term on

The output file, GEN_COUNTS_OUT.SQL, will now provide us with row counts and the table names.

set echo on
select count (*) from STUDENTS;
select count (*) from PROFESSORS;
set echo off

For this example we used three SQL*Plus commands: HEADING, PAGESIZE FEEDBACK, and TERM.

• HEADING is a session setting that suppresses the heading of SELECT results. Your options are:

  • SET HEADING ON

  • SET HEADING OFF

• PAGESIZE set to 1000 will display your output as one stream of records with no breaks. PAGESIZE of 0 suppresses a heading and breaks. If you want a heading followed by a long report with no breaks, then set PAGESIZE to a large number and SET HEADING ON.

• FEEDBACK is also a session setting that suppresses such messages as those that indicate the number of rows that were updated or selected. Your options are:

  • SET FEEDBACK ON

  • SET FEEDBACK OFF

• TERM is a useful feature that suppresses SQL*Plus output when used in conjunction with the SPOOL command. When you set TERM OFF inside a script and you are spooling output, the results are in your spool file—only. This is a valuable feature for scripts that spool output and contain many SQL statements. Your options are:

  • SET TERM ON

  • SET TERM OFF