loading BLOBs with SQL*Loader

From: "carol legros" <cleg@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 11 May 2004 14:32:21 -0400
 loading BLOBs with SQL*Loader


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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
loading BLOBs with SQL*Loader

Other related posts:
