i have a "|" separated file the 1st column of which signifies an individual record type . The record types are variable length ie record type 1 has around 100 chars while 2 has around 50 etc...

We plan to load this data in multiple tables using a loader script. The loader script works fine but loads data in only the first table . The other record types get rejected .

Some sample records from the file is as follows
1|1001725|N|2003-10-01|CON|2003-10-07|0020OTOFF|2222222|D|1229.29|983.53|.00|2|0|0|0
2|1001175|1|N|D|XT|4444*1111|549.60

Please note 1| and 2| indicate the record types mentioned above .

our control file looks like this
LOAD DATA
into table summ_order
when (1) = '1'
fields terminated by "|"
(
Record_Identifier,
OPS_Order_ID,
Record_Status,
Transaction_Date,
Booking_Status ,
Departure_Date ,
Branch_Code ,
Consultant_Ref ,
DC_Indicator ,
Total_GrossCost ,
Total_NetCost ,
Total_Discounts ,
No_of_Adults ,
No_of_Senior_Citizens,
No_of_Children ,
No_of_Infants
)
into table pay_order1
when (1) = '2'
fields terminated by "|"
trailing nullcols
(
Record_Identifier,
OPS_Order_ID,
Payment_Record_ID,
Record_Status,
DC_Indicator,
Payment_Method,
Payment_Ref,
Payment_Amount
)

The log shows the first table is populated succ. while the record type 2 shows the errors
Record 1: Discarded - all columns null.
Record 26: Discarded - all columns null.
Record 27: Discarded - all columns null.
Record 28: Discarded - all columns null.
Record 29: Discarded - all columns null.
Record 30: Discarded - all columns null.
Record 31: Discarded - all columns null.
Record 32: Discarded - all columns null.
Record 33: Discarded - all columns null.

If i remove the "trailing nullcols" criteria from the ctl file the error says something like end of logical record/column not found...

currently i have prepared multiple loader scripts for the same but this will become an issue once multiple record types are introduced in the future.

please note the record types are of variable length .

could somebody please help me as i have an approaching deadline.

regards
paresh

I have exactly the same issue. Can someone please advice on what could have caused this?

Thank You

I would suggest you post this in the appropriate forum

I'm guessing Oracle?

Oracle - dBforums