Blank spaces at the end of each record 2

[pnad]

Hello all,

I have written the following code to generate a fixed length flat file but it puts spaces at the end of each record that is outputted onto the flat file:

SET FEEDBACK off
SET HEADING off
SET PAGESIZE 0
SET LINESIZE 200
SET TERM off
SET VERIFY off

SPOOL C:\ohco\filessenttorust\PolicyInfo.txt

select rpad(companycode||''||policynumber||''||secondarycompanycode, 12)||
rpad(to_char(futureadjustmentamount,'999.99'),7)||
rpad(to_char(datefutureadjustmentmade,'yyyymmdd'),8)||
rpad(to_char(trunc(sysdate),'yyyymmdd'),8)
from ohco.policyrecord
where datefutureadjustmentmade is not null
and futureadjustmentamount is not null
and datefutureadjustmentmade between trunc(next_day(sysdate-7,'SATURDAY')) and trunc(sysdate);



SPOOL off

SET PAGESIZE 14
SET TERM on
SET VERIFY on
SET FEEDBACK on
SET HEADING on

exit

How do I get rid of the spaces at the end of each record on the file.

Any help will be appreciated.

Thanks.

 

[pnad]

Setting the linesize to 35 fixed the problem.

Thanks.

 

[LKBrwnDBA]

Instead of 'concatinating' columns and using the RPAD()/LPAD() functions, you should consider using the following:

SET COLSEP '' <= to set columnseparator to null
COL <col1> FOR A<nn> <= to set the char column width
COL <col2> FOR 9999... <= to set numeric columns

For dates you are stuck with TO_CHAR() function.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

PNAD,

There is a better method (for at least two reasons) to get rid of trailing spaces at the end of each row:

1) You don't have to manually "count" how many characters of output you have per row
2) If the rows of of unequal length, this method trims off the trailing blanks (even if a row is less than 35 characters as in your case):

set trimspool on

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

Yes trimspool is better. Should be combined also with a 'comma separated' fields (CSV) file.

But in order to generate records of a given fixed length, you need to set the linesize and each column length, and not use trimspool.

Now, for fixed-length records, remember the 'spool' command will add a 'line-feed' character (CHR(10)) at the end of each record.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

for fixed-length records, remember the 'spool' command will add a 'line-feed' character (CHR(10)) at the end of each record.

Oracle adds a CHR(10) to the end of each row of output, whether fixed-length or variable-length .csv, right LK?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

Right.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: I was just trying to remind him of that fact --


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[pnad]

Thank you all for your help.