SQL Loader - I can't believe it can't do this ...

From: Brad Lotsberg <lotsberg_at_willowglen.ab.ca>
Date: 2000/06/07
Message-ID: <393EC48B.17896C7B_at_willowglen.ab.ca>#1/1


Hi all. I am converting a database from Sybase to Oracle and am using the sql loader utility to import the flat data files generated by the Sybase bcp utility into Oracle. (The schema conversion is OK, so all the tables exist, etc.)

Some of my tables have BLOB columns into which I want to load data from the Sybase flat file that appears as double character ASCII representations (in hex). For example, if the original Sybase image column had the text "ABC", then the flat file would have "414243"

Here is the problem: The data fields in the flat file are variable width, delimited. The only sql loader data types that can deal with delimited data fields don't seem to know about ASCII-hex conversion. Just to get the table loaded, I used char(80000) as the datatype for that column in the control file, but then the BLOB get populated with literally what appears in the data file. The documentation suggests using raw or graphic as the datatype (which I believe will do the ASCII-hex conversion back to real bytes) BUT those types only work with fixed length fields.

Obviously sql loader can figure out delimited fields and it can also convert ASCII-hex back to real data bytes. Can't it do both at the same time???? This seems like such a fundamental requirement to me it is hard to believe that particular combination can't be made to work. What am I missing??

Thanks very much for any responses.

Brad Lotsberg, EET. Received on Wed Jun 07 2000 - 00:00:00 CEST

Original text of this message