How can I open a file using java stored procedure

Question

Hello all,

I am using oracle database 10g. I have to write a java stroed procedure through which i can call a file at the server and store it locally at the users machine. Im using oracle Apex 3.1.1.

Answer 1

Any reason it has to be java? DBMS_FILE in PL/SQL should generally work fine.

Otherwise, I think ( I haven't done java stored procedures for a long time ) you should just be able to open an IO stream as you always would in java.

Answer 2

If you're talking about a true Oracle Java stored procedure, I don't think you can do this. Remember that the Java procedure is running under the Oracle JVM in the database, which means that it can only "see" the database host filesystem.

Answer 3

Since you are using APEX 3.1, is there a reason why you can simply create a link to the file on the server through APEX (hence the Web Server) to download the file?

You can also do this through UTL_FILE package, just don't forget to run the CREATE DIRECTORY and grant READ on the directory first.

I think you can do the file open through java as well, but only if you do the grant first to allow the Oracle process access to the directories on your system. This does seem like the most complex method.

Answer 4

Why a Java Stored Procedure? Just because you think it cannot be done in PL/SQL?

There are a couple of FTP implementations in PL/SQL. Sourceforge has one. Tim Hall has published one on his Oracle-Base site.

[1]: http://sourceforge.net/projects/plsqlftp/ "UTL_FTP project