Re: SQL*Loader - Commit point reached - logical record count 27

hari_om_at_hotmail.com (Hari Om) wrote:

>Here are the details of my error log files:
>
>I execute the command and get following message at console:
>----------------------------------------------------------------------
>./sqlldr scott/tiger_at_common control=/full_path/test.ctl
>log=/full_path/adhoc/test.log
>
>SQL*Loader: Release 9.2.0.1.0 - Production on Tue Sep 2 10:49:27 2003
>
>Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>Commit point reached - logical record count 27
>Commit point reached - logical record count 54
>----------------------------------------------------------------------
>
>My control file is as follows:
>----------------------------------------------------------------------
>LOAD DATA
>INFILE '/full_path/hdd.csv'
>INTO TABLE h_d_t
>FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
>(
>col1,col2.......col36,rec_id_num
>)
>----------------------------------------------------------------------
>
>
>I created CSV File from my EXCEL sheet. Do I need to explicitly add
>DOUBLE QUOTES to all the VARCHAR2 data columns or would the ORACLE
>SQL*LOADER do that for me?
>
>It seems that the error points to my last column REC_ID_NUM (which is
>NOT Primary Key) Any related information on this would really be
>appreciated....
>
>Here is what my log file says:
>---------------------------------------------------------------------------
>SQL*Loader: Release 9.2.0.1.0 - Production on Tue Sep 2 08:39:55 2003
>Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>Control File: /full_path/test.ctl
>Data File: /full_path/hdd.csv
>Bad File: /full_path/hdd.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
>
>Table H_D_T, loaded from every logical record.
>Insert option in effect for this table: INSERT
>
>Column Name Position Len Term Encl Datatype
>------------------------------ ---------- ----- ---- ----
>---------------------
>COL 1 FIRST * , O(") CHARACTER
>etc...
>REC_ID_NUM NEXT * , O(") CHARACTER
>
>Record 1: Rejected - Error on table H_D_T, column REC_ID_NUM.
>ORA-01722: invalid number
>
>Record 2: Rejected - Error on table H_D_T, column REC_ID_NUM.
>ORA-01722: invalid number
>
>Record 3: Rejected - Error on table H_D_T, column REC_ID_NUM.
>ORA-01722: invalid number
>
>Record 4: Rejected - Error on table H_D_T, column REC_ID_NUM.
>ORA-01722: invalid number
>
>Record 5: Rejected - Error on table H_D_T, column REC_ID_NUM.
>ORA-01722: invalid number
>......
>
>Record 51: Rejected - Error on table H_D_T, column REC_ID_NUM.
>ORA-01722: invalid number
>
>
>MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
>
>Table H_D_T:
>0 Rows successfully loaded.
>51 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.
>----------------------------------------------------------------------
>I checked my CSV file and it seems that my last columns contains a
>valid number.
>
>Any related information on this would really be appreciated....
>
>
>THANKS!
Hi,
Have you looked in the
 /full_path/hdd.bad file to see what the data that is being rejected looks like?

Also, if any of your varchar2 fields have a comma in them, the load will fail ...You would need to enclose with "" s Received on Tue Sep 02 2003 - 20:18:24 CEST