Re: Loading GIF images into an Oracle table

i usually upload via SQL*Loader on a unix server. as i'm not a dba, and rarely access the database to this detailed a level, i really have no use for all the graphical tools. these may seem like tedious tasks, but BELIEVE ME it is well worth the effort the first time, because all you need to do is copy/modify the files, later, for future uploads.

if you id on a server, you can load the data in through SQL*Loader (provided you have SQL*Loader installed on the unix server)

make sure the data to load and the control file (explained later) are on the server, and are in your working directory (can be any)

you'll need to type
"setenv TWO_TASK <database>",
where <database> is the name of your database (note: omit the quotes and the comma when typing that in on the unix command line.

then, when you are ready to load the data, you'll need to type "sqlldr userid=scott/tiger control=controlFile.ctl", where scott/tiger are your database id and password, and controlFile.ctl is your control file.

now for the files (can be done in either order) the data file:
the order of the data in the datafile and the columns in the controlfile must coincide.
your data may look like this:

1,image1.gif,theType,theName
2,image2.gif,theType,theName
3,image3.gif,theType,theName
.

the control file:
note, the field defined as "filler" would not actually appear in your table, but is needed to load the LOB (be it a BLOB or a CLOB). also note, the OPTIONS definition can be omitted. if it is, the defaults is used. also, also, note, that anything defined in <> below is an explanation from me to you and should not be included in the controlfile OPTIONS
(

    LOG=controlFile.log,                <prints out everything you see on
the screen when SQL*Loader is executed to a file>
    BINDSIZE=50000000,                <memory allocation for the upload>
    DISCARDMAX=10,                    <number of records that are discarded
before the load fails>
    ERRORS=0,                                <number of errors allowed
before the load fails>
    ROWS=10500                            <number of rows written to the

LOAD DATA
INFILE 'datafile.dat' <path should be included if not working from the same directory>
BADFILE 'controlFile.bad' <see above comment...also, may be omitted> DISCARDFILE 'controlFile.dsc' <see above comment...also, may be omitted>

INTO TABLE images
APPEND
FIELDS TERMINATED BY ','
(

    image_id NUMBER,
    img_file_name FILLER,
    type CHAR,
    name CHAR,
    image LOBFILE(img_file_name) TERMINATED BY EOF DEFAULTIF img_file_name='?'
)

<end of control file>

note above the CHAR datatype for TYPE and NAME (as opposed to VARCHAR2). also note on the line that has the LOBFILE. the "DEFAULTIF" can also be "NULLIF" or it can be omitted.

there are also more examples in the Oracle8i/Application Developer's Guide - Large Objects (LOBs) and in Oracle8i Utilities.

i know that this was extremely wordy and verbose, but i hope it gave you some explanation.

"Sean Kubovcik" <seank_at_centurytel.net> wrote in message news:AKjE6.1520$uU6.521443_at_feed.centurytel.net...
> I created a table to store gif images to be used for a product catalog.
 The
> table layout is:
> Table name:
> IMAGES
> Columns:
> IMAGE_ID number 8
> IMAGE blob
> TYPE varchar2 50
> NAME varchar2 50
>
> I can't figure how to insert the images into my table. Can someone
> enlighten me on how to load the images into an Oracle table, such as
> previously defined? Some sample code would be great or point me in the
> right direction.
>
> Thanks in advance.
> Sean
>
>
Received on Sat Apr 28 2001 - 13:49:35 CEST