Re: sqlldr hex numbers into raw datatypes

In article <994931540.23059.0.nnrp-10.c2de4217_at_news.demon.co.uk>, "CME" says...
>
>I am trying to upload data into an oracle database using sqlldr and a comma
>seperated file from a sybase database
>
>I am having a problem loading hex numbers into raw datatypes, I was just
>wondering if this can be done, and if so how.
>
>my control file for sqlldr looks something like this:
>----------------------------
>LOAD DATA
>INFILE *
>INTO TABLE X.TBLNAME
>FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"
>(num,num2,ahexnumber)
>BEGINDATA
>1,1,0x000000000000000000000000000000000000000000000000
>-----------------------------
>
>thanks very much for any help
>
>
>

You are getting that we encountered a NON hex number -- specifically the 0x of your hex string doesn't below there. You can substr it off as such:

LOAD DATA
INFILE *
INTO TABLE TBLNAME
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" (num,
 num2,
 ahexnumber "substr( :ahexnumber, 3 )" ) BEGINDATA

1,1,0x000000000000000000000000000000000000000000000000

That'll load up just fine.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp