Re: SQL*Loader- pronlems with long datatype

This was on the comp.databases.oracle.misc about a week ago. It looks like it may help.
Start Paraphrase>>>
From: brady_at_fas.harvard.edu (Dave Brady) ...

   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
<<< End Paraphrase

Geoff Whitfield wrote:

> ...

> Can anybody tell us how to either load the data without error, or how to
> insert a CR/LF without using a replace statement (either before the load, or
> after, perhaps using an SQL statement or a PL/SQL program)?
>
> Thank you
>
> Geoff