Creating Fixed-Record Length Text File

From: Mark Curtis <mcurtis_at_state.mt.us>
Date: 5 Mar 2002 13:11:53 -0800
Message-ID: <5aba28f3.0203051311.54e0917c@posting.google.com>

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 - 15:11:53 CST

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