Re: Creating Fixed-Record Length Text File - PL/SQL Command File

in SQL*Plus,

set trimspool on

(will cut out trailing spaces, not sure how many though, never really put it to the test before).

or you can substr the value to the nearest space at the end (INSTR for a space at the end)

try them out and see what works for you.

Mark Curtis wrote:
>
> I am creating a fixed-record length text file using a PL/SQL Developer
> Command File as listed below (similar to SQL Plus script). My output
> file has an extra four spaces at the end of each record after the
> numberic field (A.POSTED_TOTAL_AMT). I've tried using the RTRIM
> function and other methods, but I can't get rid of the four spaces.
> Any ideas?
>
> SET TERMOUT ON
>
> ACCEPT FiscalYear NUMBER PROMPT 'Enter fiscal year:'
> PROMPT Building Census expenditure data file...
>
> SET TERMOUT OFF
> SET FEEDBACK OFF
> SET HEADING OFF
> SET PAGESIZE 0
> SET SPACE 0
>
> SPOOL H:\Census\Mt02exp.txt
>
> SELECT A.BUSINESS_UNIT || B.PROGRAM_VALUE_N || RPAD(A.ACCOUNT,6,' ')
> ||
> TO_CHAR(ROUND(SUM(A.POSTED_TOTAL_AMT),0),'S000000000009')
> FROM PS_LEDGER A, PS_SUBCLASS_XWLK_N B
> WHERE B.EFFDT =
> (SELECT MAX(EFFDT) FROM PS_SUBCLASS_XWLK_N
> WHERE B.SETID = SETID
> AND B.APPROPRIATION_NBR = APPROPRIATION_NBR
> AND EFFDT <= TO_DATE('30-JUN-' || &FiscalYear))
> AND B.SETID = 'STATE'
> AND A.APPROPRIATION_NBR = B.APPROPRIATION_NBR
> AND (A.LEDGER = 'ACTUALS'
> AND A.ACCOUNT LIKE '6%'
> AND A.FISCAL_YEAR = &FiscalYear
> AND A.ACCOUNTING_PERIOD <> 999)
> GROUP BY A.BUSINESS_UNIT, B.PROGRAM_VALUE_N, A.ACCOUNT
> HAVING SUM(A.POSTED_TOTAL_AMT) <> 0;
>
> SPOOL OFF
Received on Tue Mar 05 2002 - 18:24:17 CST