SQL*Loader and binary data

From: grafpoo <grafpoo_at_hotmail.com>
Date: 8 May 2003 15:43:08 -0700
Message-ID: <e67651d7.0305081443.43ef3347_at_posting.google.com>

so, does sql*loader work with binary data? i am trying to create a bulk load file of multiple records that contain binary data. the following two methods have met with failure:

1. using table oofa: create table oofa ( int a, int b, data raw(500) );

and control file:
 LOAD DATA
 INFILE 'oofa.dat' "str '|'"
 APPEND INTO TABLE OOFA
( A INTEGER, B INTEGER, DATA VARRAW(500) )
i wrote a c program that writes out the data in binary - 4 bytes per int, then 2 bytes for the varraw length, then the varraw, then the '|' separator. there seems to be a 400-byte cutoff for the varraw. with any data more than 400-bytes long, sql*loader splits the record into two, then complains about the second one that it created.

by the way, afaik the '|' separator seems to be required - i tried to do this without that and i could never load more than a single record.

2) using table oofa (i dropped the integers):  create table oofa ( data raw(500) );

and control file:
 LOAD DATA
 INFILE *
 APPEND INTO TABLE OOFA
( DATA VARRAW(500) )

 BEGINDATA
 X'05003132333435'

i get an error "variable length field exceeds maximum length" and no record loaded. i tried using X'00053132333435' just in case i'd muffed the big- little-endian stuff, but the same thing happens. Received on Fri May 09 2003 - 00:43:08 CEST