I created a SQLPLUS script to query a database and place the output into a fixed length flat file (using spool command)
I use RPAD to pad fields to the right with spaces (' ').
However, if the text in the very last field is shorter then the requested length of the field specified in RPAD, the spaces are skipped, and the record is shorter than needed.
If I use another character in RPAD, such as *, then the field is being padded to the right with that character.
I tried the both following codes for that field, but they work the same - skipping spaces:
RPAD(Zipstring, 10, ' ') and RPAD(trim(Zipstring), 10, ' ')
But RPAD(Zipstring, 10, '*') pads the field with * characters.
I am not sure if it is RPAD or SPOOL causing this, but I suspect SPOOL.
I tried to execute the SQL query from a DQL Developer and save the output to the fixed size file, and it seemed working OK.
So it probably SPOOL skipping the spaces at the end of the record.
How can I resolve it?
My simplified code is below.
sqlplus -s /nolog > /dev/null 2>&1 <<EOF
set echo off
set feedback off
set linesize 166
set pagesize 0
set sqlprompt ''
set colsep ""
set term off
set verify off
set trimspool on
rpad(trim(c.last_name), 25, ' '),
rpad(trim(c.first_name), 15, ' '),
nvl(trim(c.middle_init), ' '),
rpad(trim(c.addr1), 32, ' ') || rpad(substr(nvl(trim(c.addr2), ' '), 1, 8), 8, ' '),
rpad(trim(c.city), 25, ' '), rpad(trim(c.state), 15, ' '),
rpad(trim(c.zip), 10, ' ')
FROM MYTABLE c;