Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: sql loader error

    Hi all,
    I have an issue which is very much similar to this thread

    http://www.dbforums.com/database-con...ple-files.html

    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 posted this on OTN Discussion Forums : need help on sql loader error ... also, no replies since many hour.
    any help on this is appreciated.

    Thank You

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    OraFAQ Forum: Search For multiple tables sqlldr

    If I recall correctly, an example of how to load multiple tables exists on site above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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