Re: ORA-01460 error

From: Aya the Vampire Slayer <ryoko_at_gatech.rmv.this.part.edu>
Date: Wed, 22 Oct 2008 20:43:36 +0000 (UTC)
Message-ID: <gdo39o$434$1@news-int2.gatech.edu>

blindsey <blindsey_at_dsicdi.com> wa:
>On Oct 22, 1:35?pm, blindsey <blind..._at_dsicdi.com> wrote:
>> I'm trying to load images into an Oracle table using this technique:
>>
>> http://www.dba-oracle.com/oracle_news/2005_5_11_easy_top_loading_imag...
>>
>> 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 10.2.0.1.0, 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 10.2.0.1.0, 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.

[1] http://ugweb.cs.ualberta.ca/~c391/manual/chapt5.html

---

CREATE TABLE tb_blob_test (

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

);

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

LOAD DATA
INFILE *
INTO TABLE tb_blob_test
APPEND
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(

   file_name CHAR(30),
   blob_data LOBFILE (file_name) TERMINATED BY EOF )
BEGINDATA
ORA-20001.bmp,

• 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 10.2.0.1.0 - 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

Table TB_BLOB_TEST:
  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