PURPOSE
-------
  This article explains :-
     - Known issues with SPOOL command and
     - Some of the common SET commands used with SPOOL command
 
SCOPE & APPLICATION
-------------------
  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.

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.


Reference Articles
------------------
Note 1020334.6 How To Create A Flat Ascii File Without Rows Wrapping Around
Note 1012455.6 How To Create Column Delimited Flat File
Note 98336.1 	 How To Write Flat File From SQL*Plus?

Note 136207.1  How To Create HTML With SQL*Plus

Note 2191.1	 Including A Timestamp Or Your Own File Extension In A Spool File Name
Note 1019519.6 How To Dynamically Create File Name Used In Spool Command
Note 146129.1	 How To Change Spool File Name Dynamically

Note 130781.1  How To Assign A Windows Environment Variable Value To A Spool Filename
Note 1029440.6 How To Pass Unix Variable To Spool Command
Note 1011540.6 Remove New Line Characters From SQL*Plus Spooled Output In Unix
Note 1049804.6 HP/UX: Core Dump When Issuing "Spool" Command In SQL*Plus

Note 1018436.102 SQL*Plus Report Prints Black Box And Print Is Misaligned
Note 180795.1    SP2-0333 And How To Spool Files With Space In The Filename Or Directory Name
Note 1055492.6   How To Spool To File With .Txt Extension In Sqlplus
Note 115040.1    How To Suppress Trailing Spaces On Variable_Length Records In A Spooled File  (Set Trimspool On )
Note 154765.1    Blank Line Appears In Spool File
Note 117632.1    Getting Blank Spaces At The End Of Each Record In Spool File

Note 109161.1    How Do You Not Get The SQL Statement In A Flat File Output?
Note 100027.1    How To: Suppress Feedback In Session/Show In Spooled File

Note 123895.1    SP2-308 Using Spool Off In SQL*Plus
Note 166571.1    Error Creating Spool File In SQL*Plus Session

Note 94486.1     How To Create A SQL*Plus Spool File Larger Than 2 Gb On Unix
Bug 1715763      Upgraded From 8.0.5 To 8.1.7 And Can Nolonger Create Spool File Greater Than 2gb
Bug 1676937      HP 8.1.6.2, Request SQL*Plus Be Able To Spool Greater Than 2gb Spool File