Re: how to insert jpg images in a field of a database?

Arun wrote:

> i want to know : Hoe can i store jpg images in a database who's other
> fields are of text form .? plz provide me with the query .?
>   Does oracle 9i works on windows XP professional edition ?

Use a BLOB data type in your table and use the DBMS_LOB package to read the files and load them. I posted an example to this group about six months back but since it has been awhile ... here it is again.

• define the directory inside Oracle when logged on as SYS CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp\';
• grant read on the directory to the Dev schema GRANT READ ON DIRECTORY ctemp TO dev;
• connect as dev CONN dev/dev_at_oracle
• the storage table for the image file CREATE TABLE lob_demo ( file_name VARCHAR2(30), image BLOB) TABLESPACE ?????;
• create the procedure to load the file CREATE OR REPLACE PROCEDURE load_file (fname IN VARCHAR2) IS

src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;

BEGIN
   src_file := bfilename('CTEMP', fname);

• insert a NULL record to lock INSERT INTO lob_demo (file_name, image) VALUES (fname, EMPTY_BLOB()) RETURNING image INTO dst_file;
• lock record SELECT image INTO dst_file FROM lob_demo WHERE file_name = fname FOR UPDATE;
• open the file dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
• determine length lgh_file := dbms_lob.getlength(src_file);
• read the file dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
• update the blob field UPDATE lob_demo SET image = dst_file WHERE file_name = fname;
• close file dbms_lob.fileclose(src_file);

   COMMIT;
END load_file;
/

Daniel Morgan Received on Mon Oct 14 2002 - 10:37:54 CDT