Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Error in Loading Data with SQLLDR in Oracle 10G

691987Mar 20 2009 — edited Mar 20 2009
Hello,

Can any one suggest whats the problem in the below mentioned Control file used for loading data through SQL*LOADER

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

LOAD DATA
INFILE 'D:\test\temt.txt'
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'

INTO TABLE "TEST"
INSERT
(SRNO INTEGER(7),
PROD_ID INTEGER(10),
PROMO_ID INTEGER(10),
CHANNEL_ID INTEGER(10),
UNIT_COST INTEGER(10),
UNIT_PRICE INTEGER(10)
)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I am trying to load data in SCOTT schema as user scott.

Why do i get such an error, please refer the attach Log file.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 20 14:43:35 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: D:\test\temt.ctl
Data File: D:\test\temt.txt
Bad File: test.bad
Discard File: test.dsc
(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 "TEST", loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SRNO FIRST 7 INTEGER
PROD_ID NEXT 10 INTEGER
PROMO_ID NEXT 10 INTEGER
CHANNEL_ID NEXT 10 INTEGER
UNIT_COST NEXT 10 INTEGER
UNIT_PRICE NEXT 10 INTEGER

Record 1: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

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

Record 3: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 4: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 5: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 6: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 7: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 8: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 9: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 10: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 11: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 12: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 13: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 14: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 15: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 16: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 17: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 18: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 19: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 20: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 21: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 22: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 23: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 24: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 25: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 26: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 27: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 28: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 29: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 30: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 31: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 32: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 33: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 34: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 35: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 36: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 37: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 38: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 39: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 40: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 41: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 42: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 43: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 44: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 45: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 46: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 47: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 48: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 49: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 50: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Record 51: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table "TEST":
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.


Space allocated for bind array: 3648 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 64
Total logical records rejected: 51
Total logical records discarded: 0

Run began on Fri Mar 20 14:43:35 2009
Run ended on Fri Mar 20 14:43:43 2009

Elapsed time was: 00:00:07.98
CPU time was: 00:00:00.28



--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Below is the method of using SQLLDR and table details,


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL> desc test
Name Null? Type
----------------------- -------- ----------------
SRNO NUMBER(7)
PROD_ID NUMBER(10)
PROMO_ID NUMBER(10)
CHANNEL_ID NUMBER(10)
UNIT_COST NUMBER(10)
UNIT_PRICE NUMBER(10)




Method for using sqlldr is :

cmd promt,

d:\> sqlldr scott/tiger

control = D:\test\temt.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 20 15:55:50 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 64

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I even tried few examples,

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Which of the below mentioned Control file make sense,

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--1

LOAD DATA
INFILE 'D:\test\temt.txt'
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'

INTO TABLE "TEST"
INSERT
FIELD TERMINATED BY (,)

(SRNO INTEGER(7),
PROD_ID INTEGER(10),
PROMO_ID INTEGER(10),
CHANNEL_ID INTEGER(10),
UNIT_COST INTEGER(10),
UNIT_PRICE INTEGER(10)
)





--2

LOAD DATA
INFILE 'D:\test\temt.txt'
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'

INTO TABLE "TEST"
INSERT
FIELD TERMINATED BY (,) optionally enclosed by '"'

(SRNO INTEGER(7),
PROD_ID INTEGER(10),
PROMO_ID INTEGER(10),
CHANNEL_ID INTEGER(10),
UNIT_COST INTEGER(10),
UNIT_PRICE INTEGER(10)
)




*For code--1 i get below mentioned error..*

D:\>sqlldr scott/tiger

control = D:\test\temt.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 20 16:36:00 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-350: Syntax error at line 8.
Expecting "(", found "FIELD".
FIELD TERMINATED BY (,)
^




*And for code--2 i get the below error,*

D:\>sqlldr scott/tiger

control = D:\test\temt.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 20 16:39:22 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-350: Syntax error at line 8.
Expecting "(", found "FIELD".
FIELD TERMINATED BY (,) optionally enclosed by '"'
^
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This post has been answered by vijz on Mar 20 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 17 2009
Added on Mar 20 2009
11 comments
1,692 views