SPOOL Command Usage

This article explains :-
- Known issues with SPOOL command and
- Some of the common SET commands used with SPOOL command

SCOPE

This article is intended for SQL*Plus users.

SPOOL command in itself is a simple command. The issues are usually due to
the incorrect SQL*Plus environment setting (like linesize, heading, echo etc).

Following are some of the commands that have an impact of the output from spool.
More information can be found in SQL*Plus User's Guide and Reference documentation.

DETAILS

SET NUMWIDTH XX
---------------
   E.g. select 345.34 from dual;  
        set numwidth 2     ===> ##
        set numwidth 4     ===> 345
        set numwidth 5     ===> 345.3
        set numwidth 6     ===> 345.34
        set numwidth 10    ===>____345.34   (where _ is a space)

SET NEWPAGE {1|n|None}
----------------------
    Sets the number of blank lines to be printed from the top of each page to
    the top title.
    0    -  places a formfeed at the beginning of each page (including the
            first page) and clears the screen on most terminals.
    NONE -  SQL*Plus does not print a blank line or formfeed between the report
            pages.

SET SPACE
---------
    Determines the spacing between columns.
    E.g. set space 1 ===>  Col1_Col2   (_ is a space)
    set space 5 ===>  Col1_____Col2   (_ is a space)

SET LINESIZE
------------
    Determines the length of the line. If the linesize is too small then the
    records will wrap around to the next line and the output may not be readable.

SET PAGESIZE
------------
    Sets the number of lines per page. If set to 0, then there are no pages.

SET FEEDBACK
------------
    Displays the number of records returned by a query when a query selects at
    least n records. ON or OFF turns this display on or off. Setting feedback
    to zero is equivalent to turning it OFF.

SET HEADING
-----------
    Controls printing of column headings in reports.
    ON prints column headings in reports; OFF suppresses column headings.

SET VERIFY
----------
    Lists each line of the command file before and after substitution.

SET TERMOUT
-----------
    When running a script containing SQL commands and you have spooled the
    output and if you do not want to see the output on your screen, you can
    also add SET TERMOUT OFF to the beginning of the file and SET TERMOUT ON
    to the end of the file.

    E.g.
 
    test.sql :

      set termout off
      spool t.t0
      select* from emp;
      spool off
      exit
 
    When running this file from SQL*Plus using @test.sql, the output will not
    be display on the screen.

SET DOCUMENT
------------
    After you type DOCUMENT and enter [Return], SQL*Plus displays the prompt
    DOC> in place of SQL> until you end the documentation. The "pound"
    character (#) on a line by itself ends the documentation. If you have set
    DOCUMENT to OFF, SQL*Plus suppresses the display of the block of
    documentation created by the DOCUMENT command.

SET SQLPROMPT
-------------
    If you do not want the SQL*Plus prompt to appear in the spooled file,
    change the prompt to blank using set sqlprompt "".

SET TRIMSPOOL
-------------
    Determines whether SQL*Plus allows trailing blanks at the end of each
    spooled line. ON removes blanks at the end of each line. OFF allows
    SQL*Plus to include trailing blanks.

    E.g.

      set numwidth 38
      set NEWPAGE 0
      set SPACE 0
      set LINESIZE 80
      set PAGESIZE 0
      set FEEDBACK OFF
      set HEADING OFF
      set verify off
      set doc off
      set sqlprompt ""
      set ECHO OFF
      set trimspool  on

Spool file not created in correct directory on Windows
------------------------------------------------------
    Ensure that the SQL*Plus icon on the client PC desktop is created as a
    shortcut and has the correct working directory (usually where you want
    to create the spool file).
         
How does spool out know where to print on Unix
----------------------------------------------
    Set the following environment variables :

    ORACLE_LPARGS='lpr arguments'        e.g. -pr  printername
    ORACLE_LPPROG=/usr/local/bin/lpr'

SQLPATH and SPOOL
-----------------
    The SQLPATH registry entry or environment variable is used for the default
    location/search path for stored SQL scripts and is not used for the SPOOL
    command. This allows the user to be able to run scripts from SQL*Plus
    without specifying a path. SPOOL command does not use this.