Storing images in table?

[StickyBit]

I have a table that is composed of 5 attributes and one of the attributes needs to be an image. How would I go about linking the image on the file system to the table attribute? In other words, how would I populate the PART_IMAGE attribute in the table with images?

CREATE TABLE PART(
PART_CODE VARCHAR2(8) NOT NULL UNIQUE,
PART_DESCRIPT VARCHAR2(35),
PART_COLOUR
PART_PRICE NUMBER(8,2),
PART_IMAGE BLOB,
PRIMARY KEY (PART_CODE));

P.S. Is BLOB the correct data type to use?

Regards,

StickyBit.

 

[spenglerr]

Hi,

there are a lot of ways to do this and you can request them via MetaLink

http://metalink.oracle.com

Actually one:

1. Your database user has to be granted the "create any directory" right.
2. The database user creates a directory:
CREATE DIRECTORY imagefiles AS 'D:\Images\JPG_Files';
3. The database user addresses the files directly for loading the contents:

DECLARE
theBFile BFILE;
theBlob BLOB;
dir VARCHAR2(200) := 'IMAGEFILES';
file VARCHAR2;
BEGIN
file := &1
INSERT INTO part (
PART_CODE,
PART_DESCRIPT,
PART_COLOUR,
PART_PRICE),
PART_IMAGE)
VALUES (
seq_part_code.NextVal
,title
,colour
,price
,empty_clob()
)
RETURNING part_image INTO theBlob;

-- (3) Get a BFile handle to the external file
theBFile := BFileName(dir,file);

-- (4) Open the file
DBMS_LOB.fileOpen(theBFile);

-- (5) Copy the contents of the BFile into the empty CLOB
DBMS_LOB.loadFromFile (
dest_lob => theBlob
,src_lob => theBFile
,amount => DBMS_LOB.GETLENGTH(theBFile) );

DBMS_LOB.fileClose(theBFile);
END;

NOTE: even if you stated the DIRECTORY in lower case you will have to use UPPER CASE in the addressing BFileName-Function!!!!!

hope this helps

 

[StickyBit]

Thanks for your reply.

Regards,

StickyBit.