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.
-----------
#!/bin/ksh
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, ' ')
FROM MYTABLE c;

spool off

quit
EOF
exit
pavelmedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch8.htm#sthref1600

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 ?

Thanks

0
Naveen KumarProduction Manager / Application Support ManagerCommented:
set trimspool off -- to set it off can be done before the query gets executed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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".
0
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..."
0
pavelmedAuthor Commented:
"set trim spool off" does resolve the problem.
Thank you very much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.