Re: ORA-01460 error

From: Aya the Vampire Slayer <>
Date: Wed, 22 Oct 2008 20:43:36 +0000 (UTC)
Message-ID: <gdo39o$434$>

blindsey <> wa:
>On Oct 22, 1:35?pm, blindsey <> wrote:
>> I'm trying to load images into an Oracle table using this technique:
>> SQLLDR is kicking back all rows with the error "ORA-01460:
>> unimplemented or unreasonable conversion requested."
>> Any ideas? ?How to get around/fix?

>It's Oracle, by the way.

Okay, I got it to work but I changed some stuff from the originally posted code.

It looks like the "image_id" column was causing problems, which is not what I would've expected. I guess casting from whatever SQL*Loader considers an "INTEGER" to a NUMBER in the DB is impossible/unsupported. This site [1] lists "INTEGER" as a binary type.

In my next test after I started understanding SQL*Loader better, I changed the blob_id field to be a RAW with a default value in the DB and then completely omitted it from the control file (see below) and it worked fine. I tested the passing blob_id as a RAW type in the CTL file, but got errors, so I changed the CTL file to use a CHAR for blob_id and it worked, even though it's a RAW in the db.

After all that I changed blob_id in the DB back to a NUMBER and then tried it again with INTEGER in the CTL file but got the error again. I changed the CTL file to use CHAR for the blob_id (currently a NUMBER in the db) instead and it worked fine that way.

All I can say is that at least for Oracle, INTEGER (sql*loader) cannot be cast to NUMBER (db). Either use CHAR in the CTL file even if the field is a NUMBER in the DB, define a default value for it in the DB and omit it from the CTL file, or use some other suggestion from someone else here who knows more about it than I.


CREATE TABLE tb_blob_test (

  blob_id    RAW(16)        default sys_guid()    NOT NULL,
  file_name  VARCHAR2(30),
  blob_data  BLOB

  • CTL File contents ---------------------------

INTO TABLE tb_blob_test

   file_name CHAR(30),
   blob_data LOBFILE (file_name) TERMINATED BY EOF )

  • SQL*Loader call -----------------------------

sqlldr user/pass_at_db control=sqlldr_ctlfile.ctl log=sqlldr.log

  • Confirmation in sql*plus ---------------------

select blob_id, file_name, dbms_lob.getlength(blob_data) len from tb_blob_test;

BLOB_ID                             FILE_NAME       LEN
859AB01EA2304EA2A83BFAF2768B67B9 ORA-20001.bmp 1202230

SQL*Loader: Release - Production on Wed Oct 22 16:19:31 2008

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

Control File:   sqlldr_ctlfile.ctl
Data File:      sqlldr_ctlfile.ctl
  Bad File:     sqlldr_ctlfile.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 TB_BLOB_TEST, loaded from every logical record. Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
FILE_NAME                           FIRST    30   ,  O(") CHARACTER            
BLOB_DATA                         DERIVED     *  EOF      CHARACTER            

    Dynamic LOBFILE. Filename in field FILE_NAME

  1 Row 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:                   2048 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Wed Oct 22 16:19:31 2008
Run ended on Wed Oct 22 16:19:31 2008

Elapsed time was:     00:00:00.64
CPU time was:         00:00:00.05


"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator
Received on Wed Oct 22 2008 - 15:43:36 CDT

Original text of this message