Expert: Suchitra Joshi - 11/4/2009

Question
Respected
          mam i am using oracle10gXE
I want to store image on oracle 1og using sqlplus
i am writing this code
1)create or replace directory mydir as 'C:\';

2)insert into myimagetable (id, image) values (1, empty_blob());

1 ROW Inserted

declare
id number;
dest_lob blob;
src_lob bfile:=bfilename('mydir','sandy.jpeg');
amount integer:=dbms_lob.getlength(src_lob);
begin

select image
into dest_lob
from myimagetable
where id = 1;

dbms_lob.fileopen(src_lob);
dbms_lob.loadfromfile(dest_lob,src_lob,amount);
dbms_lob.fileclose(src_lob);

commit;
exception when others then
dbms_output.put_line(SQLERRM);
end;

sql:> Show err like this
        ORA-22285: non-existent directory or file for GETLENGTH operation

sandy.jpeg is not working then i eill try for complete path but also its not working

please suggest me

Answer
Hi San,

1) Check that you have read permission on the directory mydir. If not grant it as follows -

GRANT READ ON DIRECTORY mydir TO <your database username>;

2) Check that file sandy.jpeg is present in directory C:\

3) Change the PL/SQL code as follows -


declare
id number;
dest_lob blob;
src_lob bfile;
amount integer;
begin

src_lob :=bfilename('mydir','sandy.jpeg');
amount  :=dbms_lob.getlength(src_lob);

dbms_output.put_line('Before select ...');

select image
into dest_lob
from myimagetable
where id = 1;

dbms_output.put_line('After select ...');


dbms_lob.fileopen(src_lob);
dbms_lob.loadfromfile(dest_lob,src_lob,amount);
dbms_lob.fileclose(src_lob);

dbms_output.put_line('Before commit ...');

commit;
exception when others then
dbms_output.put_line(SQLERRM);
end;


If it still gives error, try using filename sandy.jpg or check the output (dbms output) and send me the exact location where the error occured.


Hope this helps.

Regards

Suchitra