How to store image file in Oracle 10g and how to retrieve it

[Rudra Nath]

I wanted to store image from JAVA to Oracle 10 g .
I took following steps:
created a table with column type 'BINARY FILE LOB'.
My JAVA code is as follows for update:
String query = "update mytable set bfile_loc=bfilename(\'JPG_FILES\',\'"+myImg+"\') where name= \'" +frnd +"\'";
st.executeUpdate(query);

for fetching data from table I did as follows in resultSet:
while(rs.next()){
byte[] blobbytes = rs.getBytes("bfile_loc"); text = new String(blobbytes);
}
But I am getting follwing error:
Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:519)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:410)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)


root cause
javax.servlet.ServletException: java.sql.SQLException: ORA-22289: cannot perform FILEREAD operation on an unopened file or LOB

Please help.

[Martin Vashko]

As this is very Oracle-specific question, I'll move it to the Oracle forum.

First of all, I think you should use getBlob() to access the data in the file, not getBytes(). If that fails, I'd suggest to verify the following, though I'm not particularly experienced in this:

Does the JPG_FILES (case sensitive) directory alias exist?

Has the database user executing the query proper rights (READ) on this directory?

Does the file whose name is stored in myImg variable exist in that directory?

Haven't you exceeded the maximum number of open files in the session (SESSION_MAX_OPEN_FILES)?

Also, it would be better to use PrepraredStatement to prevent SQL Injection.