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

A snippet of the data file would be helpful but i suspect that you have a text value that has a comma within it which SQL loader is recognising as a column separator. In that case you would need to enclose the offending text field with double quotes.

Jonathan

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!
Received on Wed Sep 03 2003 - 10:14:34 CEST