1 Reply Latest reply: Sep 27, 2012 8:54 AM by Frank Kulash RSS

    ORA-01722

    964860
      Hi

      I am facing an error with message ''invalid number'' when trying to insert data through sql loader


      SQL*Loader: Release 11.2.0.2.0 - Production on Thu Sep 27 19:01:14 2012

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

      Control File: /xtradb/RPMBULKUPLOAD/RPM_STAGE_PROMOTION.ldr
      Data File: /xtradb/RPMBULKUPLOAD/NICE_BULK_PROMOTION_TEMPLATE_6.csv
      Bad File: /xtradb/RPMBULKUPLOAD/NICE_BULK_PROMOTION_TEMPLATE_6.bad
      Discard File: none specified

      (Allow all discards)

      Number to load: ALL
      Number to skip: 0
      Errors allowed: 50
      Bind array: 64 rows, maximum of 256000 bytes
      Continuation: none specified
      Path used: Conventional

      Record 2: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 3: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 4: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 5: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 6: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 7: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 8: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 9: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 10: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 11: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 12: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 13: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 14: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number

      Record 15: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
      ORA-01722: invalid number


      Table "RPM_STAGE_PROMO_COMP_SIMPLE":
      0 Rows successfully loaded.
      15 Rows not loaded due to data errors.
      0 Rows not loaded because all WHEN clauses were failed.
      0 Rows not loaded because all fields were null.


      Space allocated for bind array: 255420 bytes(30 rows)
      Read buffer bytes: 1048576

      Total logical records skipped: 0
      Total logical records read: 15
      Total logical records rejected: 15
      Total logical records discarded: 0

      Run began on Thu Sep 27 19:01:14 2012
      Run ended on Thu Sep 27 19:01:17 2012

      Elapsed time was: 00:00:03.60
      CPU time was: 00:00:00.05
        • 1. Re: ORA-01722
          Frank Kulash
          Hi,

          Welcome to the forum!
          961857 wrote:
          Hi

          I am facing an error with message ''invalid number'' when trying to insert data through sql loader
          That's one of those error messages that actually means what it says.
          Record 2: Rejected - Error on table "RPM_STAGE_PROMO_COMP_SIMPLE", column TIMEBASED_DTL_IND.
          ORA-01722: invalid number
          Whatever was supposed to go into TIMEBASED_DTL_IND could not be converted to a number.
          Bad File: /xtradb/RPMBULKUPLOAD/NICE_BULK_PROMOTION_TEMPLATE_6.bad
          The "Bad File" contains copies of all the rows from the dta file where this happened. Look at that column to see why it is happening.
          Depending on the reason, you may be able to apply some kind of formatting in SQL*Loader.
          In the worst case, you could create an external file based on this same data file, with a VARCHAR2 column for the data that should go into timebased_dtl_ind, then write a MERGE or INSERT statement to get the data into RPM_STAGE_PROMO_COMP_SIMPLE. Whatever logic is necessary to format (or ignore) TIMEBASED_DTL_IND might be eaiser to do in SQL, rather than SQL*Loader.

           

          I hope this answers your question.
          If not, post a complete test script that people can run to re-create the problem and test their ideas. Include a CREATE TABLE statement for RPM_STAGE_PROMO_COMP_SIMPLE, a subset of your data file (including a few lines that cause the error, and a few that don't), and your current control file.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).