Re: Convert HEX to RAW in SQL*Loader
Date: Thu, 19 Feb 2009 10:15:07 -0800
Message-ID: <bf46380902191015of202f18p82797ccd5cdc0328_at_mail.gmail.com>
On Thu, Feb 12, 2009 at 9:43 AM, Stuart Blackburn <sblackbu_at_cbnco.com>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:
"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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 19 2009 - 12:15:07 CST