Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to load an image by using DBMS_LOB

732607Nov 11 2009 — edited Nov 23 2009
I've never used the DBMS_LOB and I've tried to write this stored procedure for reading an image file from the Unix Server and put it into a table, but I get an error.

CREATE TABLE marco_x_lob (nome varchar2(10),
immagine BLOB)
LOB (immagine) STORE AS
( TABLESPACE MEDIUM_LOB
STORAGE (INITIAL 5M NEXT 5M) )
TABLESPACE SMALL_TBL
storage (initial 100K next 100K pctincrease 0);

---------------------

CREATE OR REPLACE PROCEDURE load_marco_x_lob IS

src_file BFILE := '/users/image.jpg';
dst_file BLOB;
lgh_file BINARY_INTEGER;

begin

-- 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 marco_x_lob
set nome = 'logo',
immagine = dst_file;

-- close file
dbms_lob.fileclose(src_file);

end load_marco_x_lob;
/

---------------------

When I try to compile the stored procedure above I get "Error: PLS-00382: expression is of wrong type Line: 3 Text: src_file BFILE := '/users/formazpr/prove_lob/piemlav.jpg';"

Could you help me to use correctly the DBMS_LOB? I've got Oravle 8.1.7
Thanks.
This post has been answered by Hoek on Nov 23 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 21 2009
Added on Nov 11 2009
4 comments
1,203 views