Oracle spool formatting fields "wrong"

From: <mr.jawright_at_gmail.com>
Date: 15 Dec 2006 08:09:03 -0800
Message-ID: <1166198943.108769.284550_at_79g2000cws.googlegroups.com>

[Quoted] [Quoted] I am trying to fix a spool of the results of a query into a text file. On one of my databases this works as expected, but on another the formatting is not correct.

I am including a section of the output, but the problem may be hard to see.
The first seven columns (120 bytes) are formatted correctly, but every field after the first seven is on a new line. Does anyone have an idea what would cause this?

thanks

The query:

SELECT calendar_month                                   AS run_month


	 , TO_CHAR(taa.start_range,'mm/dd/yyyy')        AS start_range

	 , TO_CHAR(taa.end_range,'mm/dd/yyyy')          AS end_range

	 , t_v.vendor_num                               AS vendor_num

	 , t_v.name                                     AS vendor_name

	 , date_seq || t_v.vendor_num                   AS sequence_num

	 , t_ph.po_num                                  AS po_num

	 , TO_CHAR(taa.asn_count,'99999')               AS asn_count

	 , TO_CHAR(taa.recv_cartons,'9999999')          AS recv_cartons

	 , TO_CHAR(taa.recv_units,'9999999')            AS recv_units

	 , TO_CHAR(taa.audit_cartons,'9999999')         AS audit_cartons

	 , TO_CHAR(taa.audit_units,'9999999')           AS audit_units

	 , TO_CHAR(taa.audit_pct,'99999.999')           AS audit_pct

	 , TO_CHAR(taa.total_errors,'99999')            AS total_errors

	 , TO_CHAR(taa.accuracy_pct,'99999.999')        AS accuracy_pct

	 , TO_CHAR(taa.expected_asn_units,'9999999')    AS expected_asn_units


	 , TO_CHAR(taa.receive_cost,'999999999.99')     AS receive_cost


  FROM TEMP_ASN_ACCURACY taa


	 , TEMP_ASN_ACCURACY_PO_COST taa_po

	 , TEMP_ASN_ACCURACY_DATE_RANGE

	 , T_VENDOR t_v


	 , T_PO_HEADER t_ph

 WHERE taa.vendor_id = t_v.id
   AND taa.vendor_id = taa_po.vendor_id
   AND taa_po.po_id = t_ph.id
 ORDER BY taa.audit_pct DESC, taa.total_errors DESC, t_v.vendor_num;

the column formats:
SET LINESIZE 205

column "Run_Month"              format  a9 heading 'x'
column "Start_Range"            format a10 heading 'x'
column "End_Range"              format a10 heading 'x'
column "Vendor_Num"             format a10 heading 'x'
column "Vendor_Name"            format a35 heading 'x'
column "Sequence_Num"           format a26 heading 'x'
column "PO_Num"                 format a20 heading 'x'
column "ASN_Count"              format  a6 heading 'x'  -- 0         to
10000
column "Recv_Cartons"           format  a8 heading 'x'  -- 0         to
1,000,000
column "Recv_Units"             format  a8 heading 'x'  -- 0         to
1,000,000
column "Audit_Cartons"          format  a8 heading 'x'  -- 0         to
1,000,000
column "Audit_Units"            format  a8 heading 'x'  -- 0         to
1,000,000
column "Audit_Pct"              format a10 heading 'x'  -- 00000.000 to
10000.000%
column "Total_Errors"           format  a6 heading 'x'  -- 0         to
10000
column "Accuracy_Pct"           format a10 heading 'x'  -- 00000.000 to
10000.000%
column "Expected_ASN_Units"     format  a8 heading 'x'  -- 0         to
1,000,000
column "Receive_Cost"           format a13 heading 'x'  -- 0.00      to

100,000,000.00

the output:

December 11/26/200612/30/200635541     LIZ CLAIBORNE SWIM
  112620061230200635541     9308529


     4



      11



      72



       2



      48

    66.666

     8

    83.334

      56

     44251.08

December 11/26/200612/30/200605555     GROUPE CLARINS/THIERRY M
  112620061230200605555     9318341

    10

     107

     838

      10

     181

    21.599

    67

    62.984

     114                                   

  53211.20 Received on Fri Dec 15 2006 - 17:09:03 CET