Wednesday, August 13, 2008

ORA-01460: Unimplemeted conversion requested

When performing a SQL*Loader of a BLOB file an error showed up:

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST 5 INTEGER
NAME NEXT 50 , CHARACTER
IMAGE DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field NAME

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

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

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


The original controlfile used had this format:

LOAD DATA
INFILE *
INTO TABLE IMAGES
REPLACE
FIELDS TERMINATED BY ','
(
id INTEGER(5),
name CHAR(50),
image LOBFILE (name) TERMINATED BY EOF
)
BEGINDATA
001,OracleHQ1.jpg
002,oracleHQ2.jpg
003,OracleHQ3.jpg

The problem was because a data format was forced from the controlfile, just by changing the data type definition and letting SQL*Loader to have it define solved the problem, so the control file after the modification was:

LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id,
file_name,
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,OracleHQ1.jpg
002,oracleHQ2.jpg
003,OracleHQ3.jpg

No comments: