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

I don't know what you mean by a 'PL/SQL Developer Command File', but if you run your script in SQL*Plus the length of the records of the spool file is determined by the linesize parameter. In SQL*Plus you can find out the current value of this parameter with

show linesize

and reset it to the required value with

set linesize <current value minus 4>

Jaap.

On 5 Mar 2002 13:11:53 -0800, mcurtis_at_state.mt.us (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 - 16:51:07 CST