By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

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

P: n/a
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!
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
ha*****@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@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


Jul 19 '05 #2

P: n/a
On 2 Sep 2003 10:13:25 -0700, ha*****@hotmail.com (Hari Om) wrote:
Record 1: Rejected - Error on table H_D_T, column REC_ID_NUM.
ORA-01722: invalid number


Check NLS_NUMERIC_CHARACTERS environment variable.
Andrew.
Jul 19 '05 #3

P: n/a
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 <jo********@dot.state.mn.us> wrote in message news:<8j********************************@4ax.com>. ..
ha*****@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@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

Jul 19 '05 #4

P: n/a
Any other suggestions to above problem.....?
Jul 19 '05 #5

P: n/a
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(:col1))",
col2 integer external,
col3 char "ltrim(rtrim(:col3))",
col4 char "ltrim(rtrim(:col4))",
col5 char "ltrim(rtrim(:col5))",
col6 integer external,
col7 integer external,
col8 char "ltrim(rtrim(:col8))",
col9 integer external,
col10 integer external
)
----------------------------------------------------------------------

My CSV File (sample)data is as :
120,1992,08221946,02121992,02201992,405,8,,9303.48 ,0
121,1993,08221946,02121992,02201992,405,,,1303.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!
Jul 19 '05 #6

P: n/a

"Hari Om" <ha*****@hotmail.com> wrote in message
news:d1*************************@posting.google.co m...
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(:col1))",
col2 integer external,
col3 char "ltrim(rtrim(:col3))",
col4 char "ltrim(rtrim(:col4))",
col5 char "ltrim(rtrim(:col5))",
col6 integer external,
col7 integer external,
col8 char "ltrim(rtrim(:col8))",
col9 integer external,
col10 integer external
)
----------------------------------------------------------------------

My CSV File (sample)data is as :
120,1992,08221946,02121992,02201992,405,8,,9303.48 ,0
121,1993,08221946,02121992,02201992,405,,,1303.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!


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
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.