loading BLOBs with SQL*Loader

From: carol legros <cleg_at_MSRCustoms.com>
Date: Tue, 11 May 2004 14:32:21 -0400
Message-ID: <F6F855F39C1397499EC9E8469E62CDC0ECA536@exchange2000>

Hi everyone,

I am trying to populate a table called = USER_INFO using SQL*Loader. My input data file has the following = contents :

"TEST123456","TEST","abcdefgh-1","Fox&= quot;,"Test",,"C:\IMAGES\FOX.BMP"

I'm trying to load into a table defined = as follows :

 Name        =             &= nbsp;           &n= bsp;           &nb= sp;       nbs=&nbs=
p;           = Null?    Type 
 ----------------------------------------------------= -------------------
-------- ---------------- 
 UIC_ID       &nbs= p;           &nbsp= ;            =             &= nbsp;     n=&n=
bsp;         NOT NULL = CHAR(10) 
 UIC_USERID       =             &= nbsp;           &n= bsp;           &nb=
sp;           &nbs= p;        VARCHAR2(10) 
 UIC_GROUP_ID      &nbs= p;           &nbsp= ;            =             &=
nbsp;           &n= bsp;    CHAR(10) 
 UIC_FNAME       &= nbsp;           &n= bsp;           &nb= sp;          


&nbs=p;           &nbsp= ;        VARCHAR2(20) 

 UIC_LNAME       &= nbsp;           &n= bsp;           &nb= sp;          


&nbs=p;           &nbsp= ;         VARCHAR2(20) 

 UIG_SIGN       &n= bsp;           &nb= sp;           &nbs= p;          


&nbsp=;            =             = BLOB

My record is created by SQL*Loader, = however, the binary which is stored in the FOX.BMP
file is not loaded into the UIG_SIGN = blob column. The loader error is shown below and it suggests =
that the 'ext_filename' doesn't have a = value set.

I have tried modifying the filename to = include the full path name for the binary file I'm trying to
load, but no luck. I've searched = around MetaLink but not found anything beyond the obvious
things I've tried already.

I'm hoping someone can spot my error, = or point me in the right direction.

Thanks in advance,
Carol

....here's part of the SQL*Loader log = file...

Table USER_INFO, loaded from every = logical record. Insert option in effect for this = table: APPEND

Column = Name &nb= sp; Position Len Term Encl Datatype

------------------------------    &nbs= p;  ---------- ----- ---- ----
--------------------- 
UIC_ID        &nbs= p;           &nbsp= ;         = FIRST     *   ,  O(") =
CHARACTER          &nbs= p; 
UIC_USERID        =              = NEXT     *   ,  O(") = CHARACTER         


&np; 

UIC_GROUP_ID       &nbs= p;         = NEXT     *   ,  O(") = CHARACTER          nbs=&nbs=
p; 
UIC_FNAME        &= nbsp;           &n= bsp; NEXT     *   ,  O(") =
CHARACTER          &nbs= p; 
UIC_LNAME        &= nbsp;           &n= bsp; NEXT     *   ,  O(") =
CHARACTER          &nbs= p; 
"UIG_SIGN"      &n= bsp;           &nb= sp;   DERIVED     *  = EOF      =
CHARACTER          &nbs= p; 
    Dynamic = LOBFILE.  Filename in field EXT_FNAME 
EXT_FNAME        &= nbsp;           &n= bsp;       NEXT    = 40   ,  O(") =
CHARACTER          &nbs= p;

(FILLER FIELD) SQL*Loader-462: error inserting LOB = into column "UIG_SIGN", row 1, table USER_INFO
SQL*Loader-646: lob set to EMPTY in = column "UIG_SIGN", row 1, table USER_INFO
no value set for dynamic file for = column "UIG_SIGN" in table USER_INFO row 1
field for dynamic file name is = EXT_FNAME in table USER_INFO

Table USER_INFO:
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. 



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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 11 2004 - 13:30:14 CDT