Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    using sql loader to load data in multiple files

    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

  2. #2
    Join Date
    Oct 2009
    Posts
    2
    I have exactly the same issue. Can someone please advice on what could have caused this?

    Thank You

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would suggest you post this in the appropriate forum

    I'm guessing Oracle?

    Oracle - dBforums
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •