How to load an image by using DBMS_LOB
732607Nov 11 2009 — edited Nov 23 2009I'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.