Create a Flat File

SETS--

• SPACE: set spacing between columns to none
• PAGESIZE: set pagesize to 0 to turn off all headings
• TRIMSPOOL: set off so blanks at the end are not removed
• FEEDBACK: set off -- no record count needed at end
• TERM: set off the terminal to speed up the spooling
• LINESIZE: set the output file record length

Select fields as shown:

Filler Fields:
The placement of each field is dependent on the one before it. If you need a filler field, code it as '  ' (Like the 3rd field listed below.)

For alphabetic fields:
1) Always include SUBSTR function. With it, if the database column size is increased, it will be handled.
2) Use NVL on all columns that can potentially be NOT NULL. The number of spaces between the quotes should match the substr length.

For numeric fields:
1) Change it to character with TO_CHAR (This will left-justify it).
2) Left pad with zeros up to the necessary length with LPAD.

SET SPACE 0
SET PAGESIZE 0 
SET TRIMSPOOL OFF
SET FEEDBACK OFF
SET TERM OFF    
SET LINESIZE 40
/*      The spool file name should be lower case if writing to Unix.      */
SPOOL flatfile.txt

SELECT LPAD(TO_CHAR(sal),8,0)              ,
       NVL(SUBSTR(ename,1,15),'          '),
       'HELLO'                                 -- write a literal
  FROM Emp;

SPOOL OFF

/*If writing to Unix, set permissions for all to access */
-- host chmod 777 flatfile.txt