7 Replies Latest reply on Oct 30, 2012 7:56 AM by 970863

    sqlldr returns ORA-14400 although partition exists

    970863
      Hi,

      I have encountered an error with SQL Loader which I was hoping that someone here could help me out.

      I am loading records from a file which have different length. Which type of record it is is identifiable by a field called record_type and all the records in the file are loaded into the same table.

      The problem I encounter is that in average one record out of each 10000 is rejected with the errors:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-14400: inserted partition key does not map to any partition

      These records end up in a .bad file and I look at the date, which the partition is based on, then I don't find anything that can explain why it was rejected since the partition is there.

      When I try to load the content .bad file into the table then it works without any of the previously rejected records being rejected again.

      What I don't understand is why the records are rejected in the first place, but then at a second attempt successfully loaded although no modification has been done to the table (no new added, nor removed partitions). Am I somehow using the SQL loader in a way that it is not meant to be used, or might I have forgotten a setting in the .ctl file?

      Oracle version is 11.1.0.7.0.

      The control file I am using looks like this (subset, got altogether 6 different record types)
      LOAD DATA
      INFILE *
      APPEND
      INTO TABLE ROAM5
      WHEN (41:43) = 'CA3'
      TRAILING NULLCOLS
      (
      FILE_NAME POSITION(1:40) CHAR(40) "TRIM(:FILE_NAME)",
      RECORD_TYPE POSITION(41:43) CHAR(3),
      IMSI POSITION(44:58) CHAR(15) "TRIM(:IMSI)",
      IMSI_TYPE POSITION(59:60) CHAR(2) "TRIM(:IMSI_TYPE)",
      SENDER_ID POSITION(61:80) CHAR(20) "TRIM(:SENDER_ID)",
      CUSTOMER_TAG POSITION(81:134) CHAR(54) "TRIM(:CUSTOMER_TAG)",
      DATE_FROM_CDR POSITION(135:148) Date "YYYYMMDDHH24MISS",
      CALL_EVENT_DATE POSITION(149:162) Date "YYYYMMDDHH24MISS",
      TOTAL_CHARGED_UNITS POSITION(163:167) CHAR(5) "TRIM(:TOTAL_CHARGED_UNITS)",
      FINAL_CHARGE_RATE POSITION(168:179) CHAR(12) "TRIM(:FINAL_CHARGE_RATE)",
      TOTAL_CHARGEABLE_UNITS POSITION(180:184) CHAR(5) "TRIM(:TOTAL_CHARGEABLE_UNITS)",
      CURRENCY_CODE POSITION(185:189) CHAR(5) "TRIM(:CURRENCY_CODE)",
      USAGE_TYPE POSITION(190:194) CHAR(5) "TRIM(:USAGE_TYPE)",
      TAX_RATE POSITION(195:206) CHAR(12) "TRIM(:TAX_RATE)",
      CAMEL_SERVICE_LEVEL POSITION(207:207) CHAR(1) "TRIM(:CAMEL_SERVICE_LEVEL)",
      CALLED_NUMBER POSITION(208:228) CHAR(21) "TRIM(:CALLED_NUMBER)",
      ANNOTATION POSITION(229:483) CHAR(255) "TRIM(:ANNOTATION)",
      CALLING_NUMBER POSITION(484:488) CHAR(5) "TRIM(:CALLING_NUMBER)"
      )

      INTO TABLE ROAM5
      WHEN (41:43) = 'GC3'
      TRAILING NULLCOLS
      (
      FILE_NAME POSITION(1:40) CHAR(40) "TRIM(:FILE_NAME)",
      RECORD_TYPE POSITION(41:43) CHAR(3),
      IMSI POSITION(44:58) CHAR(15) "TRIM(:IMSI)",
      IMSI_TYPE POSITION(59:60) CHAR(2) "TRIM(:IMSI_TYPE)",
      SENDER_ID POSITION(61:80) CHAR(20) "TRIM(:SENDER_ID)",
      CUSTOMER_TAG POSITION(81:159) CHAR(79) "TRIM(:CUSTOMER_TAG)",
      CALLING_NUMBER POSITION(160:164) CHAR(5) "TRIM(:CALLING_NUMBER)",
      DATE_FROM_CDR POSITION(165:178) Date "YYYYMMDDHH24MISS",
      CALL_EVENT_DATE POSITION(179:192) Date "YYYYMMDDHH24MISS",
      DATE_FROM_CDR POSITION(165:178) Date "YYYYMMDDHH24MISS",
      CALL_EVENT_DATE POSITION(179:192) Date "YYYYMMDDHH24MISS",
      TOTAL_CHARGED_UNITS POSITION(193:204) CHAR(12) "TRIM(:TOTAL_CHARGED_UNITS)",
      FINAL_CHARGE_RATE POSITION(205:216) CHAR(12) "TRIM(:FINAL_CHARGE_RATE)",
      TOTAL_CHARGEABLE_UNITS POSITION(217:228) CHAR(12) "TRIM(:TOTAL_CHARGEABLE_UNITS)",
      TAX_RATE POSITION(229:240) CHAR(12) "TRIM(:TAX_RATE)",
      CURRENCY_CODE POSITION(241:245) CHAR(5) "TRIM(:CURRENCY_CODE)",
      USAGE_TYPE POSITION(246:250) CHAR(5) "TRIM(:USAGE_TYPE)",
      ANNOTATION POSITION(251:442) CHAR(192) "TRIM(:ANNOTATION)",
      APN_NI POSITION(443:447) CHAR(5) "TRIM(:APN_NI)"
      )

      The partitioning is based around a syntax like this:
      )
      TABLESPACE REVASS_DATA
      PCTUSED 0
      PCTFREE 10
      INITRANS 1
      MAXTRANS 255
      PARTITION BY RANGE (DATE_FROM_CDR)
      (
      PARTITION CDR_20121014 VALUES LESS THAN (TO_DATE(' 2012-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
      LOGGING
      NOCOMPRESS

      I am thankful for any hint or help that you could give me.

      Best regards,
      Jesper

      Edited by: 967860 on Oct 26, 2012 4:10 AM - Added Oracle version