Problem solve Get help with specific problems with your technologies, process and projects.

Coding stored procedures in Java

Is it possible to code stored procedures for Oracle databases in Java? If not, can you tell me which languages are possible?
It certainly is possible. Oracle has Java support built in (it has to be installed when the database is created or can be installed subsequently).

Here's a great example of how a Java procedure could be created to read files from a directory. Since you can't do this with PL/SQL directly, Java can do it pretty easily. This example is from guru Tom Kyte.

The interface I came up with uses a global temporary table which will "lose" its rows every time you commit. You'll call a stored procedure providing a DIRECTORY to scan and I'll put a list of all of the files that are in that directory into this temp table. If you want to "filter" the files (eg: only interested in *.txt files), you'll use SQL "select * from dir_list where filename like '%.txt'" to do so.

The implementation is:

ops$tkyte@8i> GRANT JAVAUSERPRIV to ops$tkyte
  2  /

Grant succeeded.

That grant must be given to the owner of the procedure..  Allows them to read directories.

ops$tkyte@8i> create global temporary table DIR_LIST
  2  ( filename varchar2(255) )
  3  on commit delete rows
  4  /

Table created.

ops$tkyte@8i> create or replace
  2     and compile java source named "DirList"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  7  public class DirList
  8  {
  9  public static void getList(String directory)
 10                     throws SQLException
 11  {
 12      File path = new File( directory );
 13      String[] list = path.list();
 14      String element;
 16      for(int i = 0; i < list.length; i++)
 17      {
 18          element = list[i];
 19          #sql { INSERT INTO DIR_LIST (FILENAME)
 20                 VALUES (:element) };
 21      }
 22  }
 24  }
 25  /

Java created.

ops$tkyte@8i> create or replace
  2  procedure get_dir_list( p_directory in varchar2 )
  3  as language java
  4  name 'DirList.getList( java.lang.String )';
  5  /

Procedure created.

ops$tkyte@8i> exec get_dir_list( '/tmp' );

PL/SQL procedure successfully completed.

ops$tkyte@8i> select * from dir_list where rownum < 5;


And thats it...

This was last published in October 2003

Dig Deeper on Oracle Java and J2EE

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.