Re: ORA-01460 error

From: Aya the Vampire Slayer <>
Date: Wed, 22 Oct 2008 18:48:31 +0000 (UTC)
Message-ID: <gdnshv$266$>

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.

I get the same error as you (see below), and I am using the same oracle version. It appears from the error log that it is expecting a character data LOB instead of a BLOB, and blows up when it gets to that.

I don't know much about sql*loader so I can't help you with a work-around from that end, but have you tried using dbms_lob from sql plus, such as dbms_lob.loadfromfile ? That works fine for me:

SQL*Loader: Release - Production on Wed Oct 22 14:22:13 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_TEST_BLOB, loaded from every logical record. Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
IMAGE_ID                            FIRST     5           INTEGER              
FILE_NAME                            NEXT    30   ,       CHARACTER            
IMAGE_DATA                        DERIVED     *  EOF      CHARACTER            

    Dynamic LOBFILE. Filename in field FILE_NAME

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

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

Run began on Wed Oct 22 14:22:13 2008
Run ended on Wed Oct 22 14:22:13 2008

Elapsed time was:     00:00:00.06
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 - 13:48:31 CDT

Original text of this message