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,
<pre>create or replace PROCEDURE load_file (
p_photo_name in varchar2) IS
src_file := bfilename(‘PHOTO_DIR’, p_photo_name);
— insert a NULL record to lock
INSERT INTO temp_photo
(id, photo_name, photo)
(p_id , p_photo_name ,EMPTY_BLOB())
RETURNING photo INTO dst_file;
— lock record
WHERE id = p_id
AND photo_name = p_photo_name
— open the file
— 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
SET photo = dst_file
WHERE id = p_id
AND photo_name = p_photo_name;
— close file
You can test it from SQL*Plus this way:
(Remember that the file my_image.jpg should exist in the server’s ‘c:\photo_dir’ directory)