This discussion is archived
7 Replies Latest reply: Nov 16, 2012 7:24 AM by j_DBA_sourav RSS

Commit point reached - logical record count while loading from csv file

j_DBA_sourav Newbie
Currently Being Moderated
Hi all,

I am trying to insert some rows from a csv file actually made from an excel file.
The controfile is as follows.

command used: sqlldr ***/***@DB control=file.ctl

When I am trying to insert from the control file I am getting the following error:

Commit point reached - logical record count 10
Commit point reached - logical record count 20
Commit point reached - logical record count 30
Commit point reached - logical record count 40
Commit point reached - logical record count 50
Commit point reached - logical record count 60

when I opened the log file I saw
value used for ROWS parameter changed from 64 to 10
Record 1: Rejected - Error on table TRNDTA.F09E150, column CIUPMJ.
ORA-01722: invalid number

and so on.

But when I am inserting these values manually it is getting inserted. :-(

Control file is as below:

LOAD DATA
INFILE '/path/Book1.csv'
BADFILE '/path/TASKIDBAD.TXT'
DISCARDFILE '/path/TASKIDDIS.TXT'
APPEND INTO TABLE TRNDTA.F09E150
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
CICCTN,
CIEMPLOYID,
CICRCRDNUM,
CIPMTMETH,
CICTLID,
CITRANSTAT,
CICHRGTO,
CIMCU,
CISBL,
CISBLT,
CIEXPTYPE,
CIEXPDATE,
CIEXPAMT,
CIBILLAMT,
CIEXPCRCD,
CIBILLCRCD,
CICRR,
CIBUSPURP,
CIDESCCHG,
CIREASON,
CIVENDCODE,
CIVENDNAME,
CIVSTREET,
CIVCITY,
CIVSTATE,
CIVZIPCODE,
CIVCOUNTRY,
CILOCATN,
CIREFNUMB,
CIREFDATE1,
CITICKNUMB,
CICLASSERV,
CIDESTIN1,
CIDESTIN2,
CIDESTIN3,
CIFMDATE,
CITODATE,
CINUMBDAYS,
CITELNUMB,
CITAXAMT,
CITAXTYPE,
CIUNITS,
CIRATE1,
CIORIGNUMB,
CIADDLCMT,
CIDESC01,
CIDESC02,
CIDESC03,
CIDESC04,
CINUMERIC1,
CINUMERIC2,
CINUMERIC3,
CITEXT1,
CITEXT2,
CITEXT3,
CIDATE1,
CIDATE2,
CIDATE3,
CIEXRPTTYP,
CIEXRPTNUM,
CIDTECRT,
CICCRPTYPE,
CICCRPTNUM,
CICCRPTDTE,
CICCFILENO,
CICCRECIPN,
CIGFLAG1,
CIGFLAG2,
CIGAMOUNT1,
CIGAMOUNT2,
CIGNUMBER1,
CIGNUMBER2,
CIGTEXT001,
CIGTEXT002,
CIGDATE1,
CIGDATE2,
CIGCODE1,
CIGCODE2,
CIEFLAG1,
CIEFLAG2,
CIEAMOUNT1,
CIEAMOUNT2,
CIENUMBER1,
CIENUMBER2,
CIETEXT001,
CIETEXT002,
CIEDATE1,
CIEDATE2,
CIECODE1,
CIECODE2,
CIPID,
CIUSER,
CIJOBN,
CIUPMT,
CIUPMJ
)

----------------The table where I want to insert the value is as follows:


(
CICCTN NCHAR(20) primary key,
CIEMPLOYID NUMBER,
CICRCRDNUM NCHAR(25),
CIPMTMETH NCHAR(3),
CICTLID NCHAR(30),
CITRANSTAT NCHAR(2),
CICHRGTO NCHAR(30),
CIMCU NCHAR(12),
CISBL NCHAR(8),
CISBLT NCHAR(1),
CIEXPTYPE NCHAR(4),
CIEXPDATE NUMBER(6),
CIEXPAMT NUMBER,
CIBILLAMT NUMBER,
CIEXPCRCD NCHAR(3),
CIBILLCRCD NCHAR(3),
CICRR NUMBER,
CIBUSPURP NCHAR(40),
CIDESCCHG NCHAR(45),
CIREASON NCHAR(10),
CIVENDCODE NCHAR(8),
CIVENDNAME NCHAR(35),
CIVSTREET NCHAR(40),
CIVCITY NCHAR(30),
CIVSTATE NCHAR(20),
CIVZIPCODE NCHAR(12),
CIVCOUNTRY NCHAR(30),
CILOCATN NCHAR(5),
CIREFNUMB NCHAR(20),
CIREFDATE1 NUMBER(6),
CITICKNUMB NCHAR(15),
CICLASSERV NCHAR(15),
CIDESTIN1 NCHAR(20),
CIDESTIN2 NCHAR(20),
CIDESTIN3 NCHAR(20),
CIFMDATE NUMBER(6),
CITODATE NUMBER(6),
CINUMBDAYS NUMBER,
CITELNUMB NCHAR(20),
CITAXAMT NUMBER,
CITAXTYPE NCHAR(15),
CIUNITS NUMBER,
CIRATE1 NUMBER,
CIORIGNUMB NCHAR(20),
CIADDLCMT NCHAR(60),
CIDESC01 NCHAR(45),
CIDESC02 NCHAR(45),
CIDESC03 NCHAR(45),
CIDESC04 NCHAR(45),
CINUMERIC1 NUMBER,
CINUMERIC2 NUMBER,
CINUMERIC3 NUMBER,
CITEXT1 NCHAR(40),
CITEXT2 NCHAR(40),
CITEXT3 NCHAR(40),
CIDATE1 NUMBER(6),
CIDATE2 NUMBER(6),
CIDATE3 NUMBER(6),
CIEXRPTTYP NCHAR(1),
CIEXRPTNUM NCHAR(10),
CIDTECRT NUMBER(6),
CICCRPTYPE NCHAR(25),
CICCRPTNUM NCHAR(25),
CICCRPTDTE NUMBER(6),
CICCFILENO NCHAR(15),
CICCRECIPN NCHAR(15),
CIGFLAG1 NCHAR(1),
CIGFLAG2 NCHAR(1),
CIGAMOUNT1 NUMBER,
CIGAMOUNT2 NUMBER,
CIGNUMBER1 NUMBER,
CIGNUMBER2 NUMBER,
CIGTEXT001 NCHAR(40),
CIGTEXT002 NCHAR(40),
CIGDATE1 NUMBER(6),
CIGDATE2 NUMBER(6),
CIGCODE1 NCHAR(5),
CIGCODE2 NCHAR(10),
CIEFLAG1 NCHAR(1),
CIEFLAG2 NCHAR(1),
CIEAMOUNT1 NUMBER,
CIEAMOUNT2 NUMBER,
CIENUMBER1 NUMBER,
CIENUMBER2 NUMBER,
CIETEXT001 NCHAR(40),
CIETEXT002 NCHAR(40),
CIEDATE1 NUMBER(6),
CIEDATE2 NUMBER(6),
CIECODE1 NCHAR(5),
CIECODE2 NCHAR(10),
CIPID NCHAR(10),
CIUSER NCHAR(10),
CIJOBN NCHAR(10),
CIUPMT NUMBER,
CIUPMJ NUMBER(6)
)


Please help me to overcome this. I am using sqlloader for the first time.

-Regards,
Saha
  • 1. Re: Commit point reached - logical record count while loading from csv file
    732412 Journeyer
    Currently Being Moderated
    Hello Saha.
    As this is the first time you are using SQL*Loader, there is a lot to learn. Based on the details you provided, the error you received
    Record 1: Rejected - Error on table TRNDTA.F09E150, column CIUPMJ.
    ORA-01722: invalid number
    is simply informing you that the first record supplied doesn't have a numeric value in the last column, CIUPMJ. You haven't specified a format in the control file for this column, so SQL*Loader will attempt to convert the value found from a string to a number.

    Without sample data of the failing record, all I can suggest is that you review the column's value, and evaluate whether the value would be interpreted by Oracle as a valid number. You can test this also by
    SELECT TO_NUMBER('column value') FROM dual;
    Here are some possible number formats tested using this approach and the results
    SELECT  TO_NUMBER(' 123.45') col_val  FROM  dual;
    
    COL_VAL                
    ---------------------- 
    123.45                 
    
    SELECT  TO_NUMBER(' 1,230.45') col_val  FROM  dual;
    
    SQL Error: ORA-01722: invalid number
    01722. 00000 -  "invalid number"
    
    SELECT  TO_NUMBER(' -123.45') col_val  FROM  dual;
    
    COL_VAL                
    ---------------------- 
    -123.45                
    
    SELECT  TO_NUMBER(' 123.45-')  col_val  FROM  dual;
    
    SQL Error: ORA-01722: invalid number
    01722. 00000 -  "invalid number"
    Hope this helps,
    Luke

    Please mark the answer as helpful or answered if it is so. If not, provide additional details.
    Always try to provide actual or sample statements and the full text of errors along with error code to help the forum members help you better.
  • 2. Re: Commit point reached - logical record count while loading from csv file
    j_DBA_sourav Newbie
    Currently Being Moderated
    Hi Luke,

    Thank you for your assistance. I will definitely try this out but have you notice one thing? when I am inserting the valuse manually for that field it is showing no error and the values are inserted actually. Do you still think then it could be the problem with number value. The first line is showing me the error and when I am inserting the same value manually it is fine with that.. That was even more confusing :-/


    -Regards,
    Saha
  • 3. Re: Commit point reached - logical record count while loading from csv file
    732412 Journeyer
    Currently Being Moderated
    Hello Saha.
    I'm unable to comment on your statement regarding the values being able to be inserted manually as no specifics have been provided.

    What you could do, is provide a rejected record from the bad data file and the corresponding insert statement you are issuing to insert manually. This would allow me the opportunity to review the data and comment.

    One item to consider is that the data record may not contain the proper number of columns, less or more, and therefore SQL*Loader is actually attempting to load another column's value, perhaps one with text, into the table's numeric column and is therefore providing the error. This is not uncommon with comma delimited files when some data in a column may occasionally include a comma as well. This is frequently addressed through use of enclosing characters in addition to the comma delimiters.


    Hope this helps,
    Luke

    Please mark the answer as helpful or answered if it is so. If not, provide additional details.
    Always try to provide actual or sample statements and the full text of errors along with error code to help the forum members help you better.
  • 4. Re: Commit point reached - logical record count while loading from csv file
    hm Expert
    Currently Being Moderated
    Hi,

    as I see in your control-file the field are separated by ,

    A possible problem can be, that there are also ','-characters inside the values of other columns.
    So the detection of field-position in your data-file will be wrong.

    You could write this in your control-file for that case:
    fields terminated by ',' optionally enclosed by '"'     
    {code}
    
    Of cause you have to enclose your fields that contain a ',' with quotes then.
    E.g.
    
    {code}
    1,2,3,"4,4",5
    {code}
    
    Edited by: hm on 28.07.2011 23:55                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 5. Re: Commit point reached - logical record count while loading from csv file
    user130038 Pro
    Currently Being Moderated
    If you are still stuck on this issue, please post some sample data [ both that you used for loading *manually* and through a *csv* file). Plz also post the BADFILE contents.                                                                                                                                                                                                                                                                                                                                                           
  • 6. Re: Commit point reached - logical record count while loading from csv file
    j_DBA_sourav Newbie
    Currently Being Moderated
    thnx all.. It was sloved..

Legend

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