More Oracle Answers
Question Library
Ask a question about Oracle
Volunteer
Experts of the Month
Expert Login
Awards
About Us
Tell friends
Link to Us
Disclaimer
|
| |
|
|
| |
| | | |
About Suchitra Joshi
Expertise
I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader, Oracle Forms, Oracle Reports, and some basic dba and performance tuning activities.
Experience
15+ years of Oracle PL/SQL Development
Education/Credentials
B.Sc (Electronics), Diploma in Computer Applications (DCA)
Awards and Honors
OCP - SQL and PL/SQL
Brainbench certifications in Oracle Administration, PL/SQL, Developer 2000
| | |
| |
You are here: Experts > Computing/Technology > Oracle > Oracle > save image using oracle10gXE
Oracle - save image using oracle10gXE
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
Add to this Answer Ask a Question
|
|
