[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3208
  • Last Modified:

SPOOL or RPAD skips padded spaces in fixed size output

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
connect myusername/mypassword

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

spool /mydirectory/myfilename
SELECT distinct
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, ' ')

spool off

  • 2
  • 2
  • 2
1 Solution
pavelmedAuthor Commented:
A little correction to my code above:
the line "set linesize 166"
should be "set linesize 131"
I just forgot to recalculate the size the that simplified code where I omitted some fields.

Naveen KumarProduction Manager / Application Support ManagerCommented:
see the link and search for "SET TRIMSPOOL ON"


SET TRIMSPOOL ON removes trailing blanks at the end of each displayed or spooled line.

Can you remove and test it once to see if that can resolve your issue ?


Naveen KumarProduction Manager / Application Support ManagerCommented:
set trimspool off -- to set it off can be done before the query gets executed.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Mark GeerlingsDatabase AdministratorCommented:
That is correct, the problem is this line: "set trimspool on".  That will trim (remove) trailing blanks from the end of a line *IF* you are spooling the output.  So, there is no problem with RAD, that does the requested spaces, but before you see the output, trimspool has removed them from the end of the line.

By default in SQL*Plus trimspool is off, so you could just remove the trimspool line from your file, or to be safe, leave it in, but as: "set trimspool off".
Mark GeerlingsDatabase AdministratorCommented:
Oops!  I missed a character and a word in my previous post.  That was intended to be: "So, there is no problem with RPAD, that does add the requested spaces..."
pavelmedAuthor Commented:
"set trim spool off" does resolve the problem.
Thank you very much!

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now