How to insert an image file into an Oracle 8i database?

5 pts.
Tags:
BLOB
Oracle 8i
Oracle 8i image storage
Oracle administration
Oracle Forms
Oracle Forms 6i
Oracle Forms and Reports 6i
Oracle image storage
Oracle Reports
Oracle Reports 6i
Hi Experts, I am using forms&reports6i with the oracle 8i database. Now i want to insert a image file into the database and i want to access that in reports. I tried the insert statement but i couldn't. can anyone help me for inserting.

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you use Forms, you could easily insert the image into the database this way:

- create a database block for the table (if you use the block wizard, the item corresponding to the BLOB field is automatically created as an image item)
- create a button or some other control to call the read_image_file built-in
- run the form and query the desired record (or create a new one)
- press the button to load the image into the form
- save (commit) the record.
- perform a query (enter-query -> execute-query), and you will see the image.

if you don’t want to insert the image by using Forms, it is a little more complicated.

First, you need to create a directory on the database (which is mapped to a directory in the server’s filesystem). The user must be granted the CREATE ANY DIRECTORY privilege.

Something like this:

<pre>create directory photo_dir as ‘c:\photo_dir’;</pre>

Then you need to write a stored procedure to insert the record.

This is the one I used (it works):

Create this table if you want to test it:

<pre>CREATE TABLE TEMP_PHOTO
(
ID NUMBER(3) NOT NULL,
PHOTO_NAME VARCHAR2(50),
PHOTO BLOB
);</pre>

<pre>create or replace PROCEDURE load_file (
p_id number,
p_photo_name in varchar2) IS

src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;

BEGIN
src_file := bfilename(‘PHOTO_DIR’, p_photo_name);

— insert a NULL record to lock
INSERT INTO temp_photo
(id, photo_name, photo)
VALUES
(p_id , p_photo_name ,EMPTY_BLOB())
RETURNING photo INTO dst_file;

— lock record
SELECT photo
INTO dst_file
FROM temp_photo
WHERE id = p_id
AND photo_name = p_photo_name
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 temp_photo
SET photo = dst_file
WHERE id = p_id
AND photo_name = p_photo_name;

— close file
dbms_lob.fileclose(src_file);
END load_file;</pre>

You can test it from SQL*Plus this way:

<pre>execute load_file(1,’my_image.jpg’);</pre>

(Remember that the file my_image.jpg should exist in the server’s ‘c:\photo_dir’ directory)

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • KSPH
    Hi Carlosdl , Am trying to insert file into database table. Can u please tell me where the photo_dir should reside ?
    10 pointsBadges:
    report
  • carlosdl
    It would be in the server. You define where it is, when you create the directory in the database, with this command: create directory photo_dir as 'c:photo_dir';
    68,035 pointsBadges:
    report
  • Zakariasal
    Hello; I don't want to insert a specifc image into my form, i want the user to be able to select the picture to save from the server's file system instead. (like browsing) how can i do that?? thanks:)
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following