OraFAQ Forum: Server Administration » Re: how to save an image into a table ?

Home » RDBMS Server » Server Administration » Re: how to save an image into a table ?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Re: how to save an image into a table ? [message #373713]

Wed, 02 May 2001 11:57

Doug
Messages: 7
Registered: May 2001

Junior Member

I happened to have saved this information from the web last night, and it might help you:

From: Green Lady (lilgreen@bellsouth.net)
Subject: Re: Loading GIF images into an Oracle table
Newsgroups: comp.databases.oracle.tools
Date: 2001-04-28 04:40:33 PST

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
database before a commit is issued>
)

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.

Report message to a moderator

Previous Topic:	Zoned Decimal from Mainframe
Next Topic:	Recursive SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 00:25:55 CDT 2024