Re: Loading files into LONG RAW column

>> I need help with loading a file into a LONG RAW column:
>> Does anybody know the way to perform this operation from UNIX OS?
>>
>> Is there any utility? Could it be performed from PL/SQL?

   I no longer have the full text of this post, so I don't know how applicable my response will be, but here it is anyway.

   I recently spent a week-and-a-half trying to get SQL*Loader to load variable length JPEGs into a table. It's a *pain*, but it can be done.

   My table structure:

      picture_num CHAR(8) NOT NULL,
      picture     LONG RAW

   My SQL*Loader control file:
     OPTIONS (DIRECT = TRUE, ERRORS = 0, SILENT = ERRORS)
     UNRECOVERABLE

     CONTINUE_LOAD DATA
     INFILE '/data/picture.data' "VAR"

     INTO TABLE id_picture
       (picture_num TERMINATED BY "|",
        picture VARGRAPHIC)

   Note the "VAR" option on INFILE. This I got from Oracle Tech. Supp. (it's *not* documentated in the Oracle7 Server Utilities book. Grrrr.). This option tells SQL*Loader to allow carriage returns in a field. Its normal be- havior to terminate a logical record on the first carriage return.

   I used VARGRAPHIC as it allows for variable length data, and SILENT = ERRORS because you'll get lots of (incorrect) warnings about length problems.

   The relevent lines from my perl script:

# The "00" tacked on to the beginning of the JPEG
# is a length specifier (supposedly) used by the
# SQL*Loader VARGRAPHIC data type in the SQL*Loader
# control file.
# (I couldn't see that setting it made any difference,
# so I set to it zero for every record.)
     $picture_entry = "$picture_num|00$binary_jpeg\n";

# We now get the total length of this PICTURE_NUM/JPEG
# pair, and put this value at the beginning of the
# record. This value is used by the "VAR" option on
# the INFILE parameter in the SQL*Loader control file.

     #


     # It tells SQL*Loader how long the logical record is,


     # so that newlines will be seen as part of the record,


     # and not as an end-of-record.    


     $length_picture_entry = length $picture_entry;



     # The record length specifier must be five digits.


     while ($length_picture_entry !~ /\d{5}/) {
       $length_picture_entry = '0' . $length_picture_entry;
     }

     print "$length_picture_entry$picture_entry";

   I *think* that's everything. Hope it helps.

   Any questions/corrections, please contact me!

--

Dave Brady                 FAS Computer Services
Sr UNIX Sys Admin/DBA      Harvard University
brady_at_fas.harvard.edu      LL1A Science Center
Phone: (617) 495-1273      1 Oxford Street
FAX:   (617) 495-1210      Cambridge, MA 02138