On Thu, Feb 12, 2009 at 9:43 AM, Stuart Blackburn <sblackbu@xxxxxxxxx>wrote: > Has anyone ever successfully converted from a HEX string to a BLOB in > SQL*Loader? My data file contains long HEX strings which represent a .JPG > file and I need this to be loaded into a table containing a BLOB column. > > If my control file contains the following line it loads the HEX string as > text which is incorrect: > C1 POSITION(1) varrawc(6,100000) > If my control file contains the following line I receive a "SQL*Loader-309: > No SQL string allowed as part of C1 field specification" error > C1 POSITION(1) varrawc(6,100000) "HEXTORAW(:C1)" > There is one useful reference to this in MetaLink: https://metalink2.oracle.com/metalink/plsql/f?p=200:27:7434780497869951239::::p27_id,p27_show_header,p27_show_help:288603.999,1,1 "SQL functions are not supported against LOB columns with SQL*Loader" I can't find that statement in the docs, but I didn't spend a lot of time reading them. A workaround for this is to create the column as LONG or LONG RAW, whichever is appropriate, and then load the data. ALTER TABLE can be used to convert to a LOB alter table blob_test modify( clob_column clob); If this data is being loaded into an existing table with data in it, you will need a staging table. I just tested this on a 10.2 database and the process works. Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist