How to add a line feed to your spool file

If you want to spool out a file from you sqlplus session, and need a line feed in your string, you can use the function

char(int)

to concatenate a line feed into your string. Integer represents the decimal value of the character you'd like to send to the database.

Example:

set trimspool on
set lines 200
set pages 0
set heading off
set verify off
set echo off
set feedback off
spool rowcount.sql
select 'spool rowcount.out' from dual;
select 'prompt rowcount for ' || owner || '.' || table_name || chr(10)|| 'select count(*) from ' || owner || '.'  || table_name || ';'
from dba_tables
where owner='SCOTT';
select 'exit' from dual;
exit

The chr(10) will return a line feed (may not be apparent in the code below but there is a line feed in there):

SYS@fsprod SQL> select chr(10) from dual;

C
-

The chr(10) corresponds to the hexadecimal value of A, which in turns corresponds to a line feed under the characterset that I happen to use in this database, which is WE8ISO8859P1.


The result is a file which looks like this:

spool rowcount.out
prompt rowcount for SCOTT.EMP
select count(*) from SCOTT.EMP;

prompt rowcount for SCOTT.DEPT
select count(*) from SCOTT.DEPT;

prompt rowcount for SCOTT.BONUS
select count(*) from SCOTT.BONUS;
.
.
.
exit

Sources: ISO code pages