Oracle SPOOL Command: Reliable Output, Logs, and Exports

Quick Start (SQL*Plus / SQLcl)

-- 1) Connect, then choose a path (quote if spaces in path)
SET TERMOUT OFF            -- don't echo results to screen while spooling (optional)
SET ECHO OFF               -- don't echo commands into the file (set ON for debugging)
SET FEEDBACK ON            -- show "n rows" footer
SET PAGESIZE 5000
SET LINESIZE 200
SET TRIMSPOOL ON           -- trim trailing spaces
SPOOL /tmp/query_output.txt

\-- 2) Your work
SELECT employee\_id, first\_name, last\_name, salary
FROM   hr.employees
WHERE  department\_id = 10
ORDER  BY last\_name;

\-- 3) Finish
SPOOL OFF
SET TERMOUT ON 

Production-friendly Logging Template

-- log_run.sql
SET SERVEROUTPUT ON SIZE UNLIMITED
SET ECHO ON
SET TIMING ON
SET SQLBLANKLINES ON
SET PAGESIZE 0
SET LINESIZE 400
SET TRIMSPOOL ON
COLUMN now NEW_VALUE run_ts
SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD"T"HH24MISSFF3TZH:TZM') now FROM dual;

SPOOL logs/run\_\&run\_ts..log

PROMPT === Begin run at \&run\_ts
\-- your scripts here
@01\_prepare.sql
@02\_report.sql
@03\_cleanup.sql

PROMPT === End run at \&run\_ts
SPOOL OFF 

Export Patterns

Plain text (portable)

SET PAGESIZE 0
SET LINESIZE 400
SET TRIMSPOOL ON
SPOOL /tmp/employees.txt
SELECT employee_id || '|' || last_name || '|' || salary AS line
FROM   hr.employees
ORDER  BY employee_id;
SPOOL OFF

CSV (SQL*Plus ≥ 12.2 via MARKUP)

SET MARKUP CSV ON DELIMITER , QUOTE ON
SET PAGESIZE 0
SPOOL /tmp/employees.csv
SELECT employee_id, first_name, last_name, salary
FROM   hr.employees
ORDER  BY employee_id;
SPOOL OFF
SET MARKUP CSV OFF

CSV (SQLcl)

SET SQLFORMAT csv
SPOOL /tmp/employees.csv
SELECT employee_id, first_name, last_name, salary
FROM   hr.employees;
SPOOL OFF
SET SQLFORMAT default

HTML (for quick sharing)

SET MARKUP HTML ON ENTMAP ON
SPOOL /tmp/report.html
SELECT * FROM hr.departments ORDER BY department_id;
SPOOL OFF
SET MARKUP HTML OFF

Using SPOOL in SQL Developer

• Open a worksheet, press F5 (Run Script). SQL*Plus-style commands (SPOOL, SET) are honored in script mode.
• Alternatively, for ad-hoc exports use the grid export (right-click result grid → Export)—this does not use SPOOL and has its own formats (CSV, Excel, etc.).

Common Options Cheatsheet

-- visibility and formatting
SET ECHO {ON|OFF}
SET TERMOUT {ON|OFF}
SET FEEDBACK {ON|OFF}
SET HEADING {ON|OFF}
SET PAGESIZE n
SET LINESIZE n
SET TRIMSPOOL {ON|OFF}
SET COLSEP ','           -- column separator for text output

\-- file handling
SPOOL file               -- overwrite or create
SPOOL APPEND file        -- append if supported by client
SPOOL OFF 

Paths, Quoting, and OS Notes

• UNIX/Linux: /tmp/out.txt, $HOME/out.txt
• Windows: C:\logs\out.txt or quote paths: SPOOL "C:\Users\you\My Reports\out.txt"
• Ensure the directory exists and you have write permission—SP2-0606 errors commonly indicate a bad path or permissions.

Troubleshooting

• File is empty: Verify SPOOL was started before your queries and ended with SPOOL OFF. Check TERMOUT OFF isn’t suppressing expected screen messages you rely on for validation.
• Weird wrapping: Increase SET LINESIZE and use TRIMSPOOL ON. For CSV, prefer MARKUP/SQLFORMAT to avoid spacing issues.
• Headers/footers in file: Tune HEADING, FEEDBACK, and PAGESIZE for your target format.
• Locale/decimal issues: For CSV interchange, set consistent NLS (e.g., ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,').

Security & Compliance

• Avoid spooling secrets to shared paths. Prefer locked directories and rotate logs.
• Include timestamps in filenames (TO_CHAR(SYSTIMESTAMP,...)) and capture ENV/WHOAMI context in the log header if policy requires.

Mini Checklist

1. Pick text vs CSV vs HTML based on consumer.
2. Set PAGESIZE/LINESIZE/TRIMSPOOL for clean output.
3. Use MARKUP (SQL*Plus) or SQLFORMAT (SQLcl) for CSV.
4. Script runs: enable ECHO, TIMING, and timestamped filenames.
5. Always SPOOL OFF and verify bytes written.

Spool Command - Exercise