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

**Turkbear** · Jul 19 '05, 09:04 PM

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

hari_om@hotmail .com (Hari Om) wrote:
[color=blue]
>Here are the details of my error log files:
>
>I execute the command and get following message at console:
>----------------------------------------------------------------------
>./sqlldr scott/tiger@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![/color]
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

**Wegorz** · Jul 19 '05, 09:04 PM

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

On 2 Sep 2003 10:13:25 -0700, hari_om@hotmail .com (Hari Om) wrote:[color=blue]
>Record 1: Rejected - Error on table H_D_T, column REC_ID_NUM.
>ORA-01722: invalid number[/color]

Check NLS_NUMERIC_CHA RACTERS environment variable.
Andrew.

**Hari Om** · Jul 19 '05, 09:04 PM

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

Thanks John.

DO I HAVE TO EXPLICITLY ADD DOUBLE QUOTES to the CSV FILE to all
fields that have COMMA....?

One more thing....
I have around 20000 records in my CSV file.

Also my log file shows that the data types are CHARACTER but some of
my columns in Table are number(10) and number(10,2)... .what do I need
to do with them.....? will Oracle take care of this automatically.. ..?

But then in my contorl file I explicitly mention datatypes as INTEGER
etc...but then I get following error:
-------------------------------------------------------------------
Record 1: Rejected - Error on table H_D_T.
ORA-01460: unimplemented or unreasonable conversion requested

Record 2: Rejected - Error on table H_D_T.
ORA-01460: unimplemented or unreasonable conversion requested
-------------------------------------------------------------------

Any related information on this is appreciated.

THANKS!

Turkbear <john.greco@dot .state.mn.us> wrote in message news:<8jn9lvkqg qd1ng594g45p8r7 cjs97i97k2@4ax. com>...[color=blue]
> hari_om@hotmail .com (Hari Om) wrote:
>[color=green]
> >Here are the details of my error log files:
> >
> >I execute the command and get following message at console:
> >----------------------------------------------------------------------
> >./sqlldr scott/tiger@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![/color]
> 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[/color]

**Hari Om** · Jul 19 '05, 09:05 PM

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

Any other suggestions to above problem.....?

**Hari Om** · Jul 19 '05, 09:05 PM

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

Here is my updated version of my execution.....

SQL> desc h_d_t
Name Type
----------------------------------------------------------------------
col1 VARCHAR2(3)
col2 NUMBER(4)
col3 VARCHAR2(8)
col4 VARCHAR2(8)
col5 VARCHAR2(8)
col6 NUMBER(3)
col7 NUMBER(8)
col8 VARCHAR2(1)
col9 NUMBER(10,2)
col10 NUMBER(3)

My control file is:
----------------------------------------------------------------------
LOAD DATA
INFILE '/full_path/hdd.csv'
INTO TABLE h_d_t
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
col1 char "ltrim(rtrim(:c ol1))",
col2 integer external,
col3 char "ltrim(rtrim(:c ol3))",
col4 char "ltrim(rtrim(:c ol4))",
col5 char "ltrim(rtrim(:c ol5))",
col6 integer external,
col7 integer external,
col8 char "ltrim(rtrim(:c ol8))",
col9 integer external,
col10 integer external
)
----------------------------------------------------------------------

My CSV File (sample)data is as :
120,1992,082219 46,02121992,022 01992,405,8,,93 03.48,0
121,1993,082219 46,02121992,022 01992,405,,,130 3.08,0

I execute my SQL*Loader as follows:
----------------------------------------------------------------------
../sqlldr scott/tiger@common control=/full_path/test.ctl
log=/full_path/test.log discard=/full_path/hdd.dis

SQL*Loader: Release 9.2.0.1.0 - Production on Wed Sep 3 08:33:06 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 36
Commit point reached - logical record count 72
----------------------------------------------------------------------

My LOG File is as follows:
-----------------------------------------------------------------------
Record 1: Rejected - Error on table H_D_T, column COL1.
ORA-01460: unimplemented or unreasonable conversion requested

Record 2: Rejected - Error on table H_D_T.
ORA-01460: unimplemented or unreasonable conversion requested

Record 3: Rejected - Error on table H_D_T.
ORA-01460: unimplemented or unreasonable conversion requeste
.....
Record 51: Rejected - Error on table H_D_T.
ORA-01460: unimplemented or unreasonable conversion requeste
----------------------------------------------------------------------

Questions:
-----------
1) Is the above definition of the Table H_D_T and one in Control file
fields proper?
2) For VARCHAR2 types do I need to specify the length in Control file?
3) What about NUMBER Datatype columns...? Is declaring them as
"INTEGER EXTERNAL"
sufficent....? DO I need to typecast them....?
4) What about NUMBER(10,2)... how do I represent these in Control
file...?

Any relate dinformation is appreciated...

THANKS!

**Eric Parker** · Jul 19 '05, 09:05 PM

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

"Hari Om" <hari_om@hotmai l.com> wrote in message
news:d1d5ebe4.0 309030657.78399 51@posting.goog le.com...[color=blue]
> Here is my updated version of my execution.....
>
> SQL> desc h_d_t
> Name Type
> ----------------------------------------------------------------------
> col1 VARCHAR2(3)
> col2 NUMBER(4)
> col3 VARCHAR2(8)
> col4 VARCHAR2(8)
> col5 VARCHAR2(8)
> col6 NUMBER(3)
> col7 NUMBER(8)
> col8 VARCHAR2(1)
> col9 NUMBER(10,2)
> col10 NUMBER(3)
>
> My control file is:
> ----------------------------------------------------------------------
> LOAD DATA
> INFILE '/full_path/hdd.csv'
> INTO TABLE h_d_t
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> (
> col1 char "ltrim(rtrim(:c ol1))",
> col2 integer external,
> col3 char "ltrim(rtrim(:c ol3))",
> col4 char "ltrim(rtrim(:c ol4))",
> col5 char "ltrim(rtrim(:c ol5))",
> col6 integer external,
> col7 integer external,
> col8 char "ltrim(rtrim(:c ol8))",
> col9 integer external,
> col10 integer external
> )
> ----------------------------------------------------------------------
>
> My CSV File (sample)data is as :
> 120,1992,082219 46,02121992,022 01992,405,8,,93 03.48,0
> 121,1993,082219 46,02121992,022 01992,405,,,130 3.08,0
>
> I execute my SQL*Loader as follows:
> ----------------------------------------------------------------------
> ./sqlldr scott/tiger@common control=/full_path/test.ctl
> log=/full_path/test.log discard=/full_path/hdd.dis
>
> SQL*Loader: Release 9.2.0.1.0 - Production on Wed Sep 3 08:33:06 2003
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Commit point reached - logical record count 36
> Commit point reached - logical record count 72
> ----------------------------------------------------------------------
>
> My LOG File is as follows:
> -----------------------------------------------------------------------
> Record 1: Rejected - Error on table H_D_T, column COL1.
> ORA-01460: unimplemented or unreasonable conversion requested
>
> Record 2: Rejected - Error on table H_D_T.
> ORA-01460: unimplemented or unreasonable conversion requested
>
> Record 3: Rejected - Error on table H_D_T.
> ORA-01460: unimplemented or unreasonable conversion requeste
> ....
> Record 51: Rejected - Error on table H_D_T.
> ORA-01460: unimplemented or unreasonable conversion requeste
> ----------------------------------------------------------------------
>
> Questions:
> -----------
> 1) Is the above definition of the Table H_D_T and one in Control file
> fields proper?
> 2) For VARCHAR2 types do I need to specify the length in Control file?
> 3) What about NUMBER Datatype columns...? Is declaring them as
> "INTEGER EXTERNAL"
> sufficent....? DO I need to typecast them....?
> 4) What about NUMBER(10,2)... how do I represent these in Control
> file...?
>
> Any relate dinformation is appreciated...
>
> THANKS![/color]

Hari

I can only assume you've not supplied the failing records in your sample.
I've just run the SQL*Loader using your supplied info with the result

----------------
Table H_D_T:
2 Rows successfully loaded.
0 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: 165120 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Wed Sep 03 16:48:43 2003
Run ended on Wed Sep 03 16:48:43 2003

Elapsed time was: 00:00:00.14
CPU time was: 00:00:00.06
-----------------

How about supplying the duff data ?

eric

--
Remove the dross to contact me directly

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

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

Comment

Comment

Comment

Comment

Comment

Comment