This discussion is archived
1 Reply Latest reply: Sep 27, 2012 6:54 AM by Frank Kulash RSS

ORA-01722

ali mirza Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points