Creating an Oracle stored procedure in Java

For haters of PL/SQL, you can create Oracle stored procedures in Java. Here are some notes on how to do it, cobbled together from pages I found on the web plus my own trial and error...

1. Add a Java schema entity:



create or replace and compile java source named "test" as
import java.sql.*;
import oracle.jdbc.*;
public class test {
public static void test(String name) {
System.out.println("Doing something... " + name);
}
}

2. The above doesn't do anything. You can add JDBC handling code, and when you get the connection, if you use the special connection string "jdbc:default:connection:" this will automatically connect to the database in which the class has been stored.

3. Run the above, which should compile the Java class and store it in the database.

4. You can see if it's been stored by doing:



select object_name,object_type,status from user_objects where object_type like 'JAVA%';

If it compiled OK, status should be 'VALID'.

5. Now you have to create a normal Oracle procedure to wrap the method call. E.g:



create or replace procedure test(name varchar2) as language java name 'test.test(java.lang.String)';


If it's not working, check if there were any errors by using "show err;" in SQLPlus or whatever...

6. Now you can run it. Do the following which should make sure you actually see the output...



call dbms_java.set_output(2000);
set serveroutput on;
call test('paul');

You should see your method output in the console...

Pages I referred to for this, thanks to their authors!:

• http://programmersjournal.blogspot.com/2008/04/writing-java-stored-procedure-in-oracle.html (includes useful sample JDBC code which actually does something, and has more detail generally!)
  
• http://www.dbasupport.com/forums/archive/index.php/t-19941.html (helped me realize that I needed to declare the argument types in Oracle form (varchar2 etc.) and Java form (java.lang.String etc.) in the procedure which wraps the method call. This wasn't immediately apparent to me...