SQL*LOADER - Commit point reached - logical record count 27...?

From: Hari Om <hari_om_at_hotmail.com>
Date: 2 Sep 2003 10:11:15 -0700
Message-ID: <d1d5ebe4.0309020911.6e858c82_at_posting.google.com>



[Quoted] 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! Received on Tue Sep 02 2003 - 19:11:15 CEST

Original text of this message