XP-rience

Something that I'd like to share with you!

Thursday, October 15, 2009

Setting up a simple Java stored procedure/function in Oracle

leorick Thursday, October 15, 2009 , No comments :
Starting with Oracle 8i, we can write Java Stored Procedures/Functions within the database. Stored procedures/functions are Java methods published to SQL and stored in the database for general usage. It also could be called by a trigger. Example; to send an email whenever a specific row has been inserted into the table.

Run the $ORACLE_HOME/javavm/install/initjvm.sql script from SYS AS SYSDBA to install the Oracle JServer Option on a database.

oracle@myserver:~> sqlplus '/ as sysdba' @$ORACLE_HOME/javavm/install/initjvm.sql


(This process is reversible. It could be done by running the rmjvm.sql script to uninstall the Oracle JServer Option from the database)

Grant JAVAUSERPRIV to selected user.

oracle@myserver:~> sqlplus '/ as sysdba'
SQL> GRANT JAVAUSERPRIV TO theuser;

Grant succeeded.


You could use a console but I'm using Toad by Quest Software for the next step. Create Java source from TOAD by login to the Oracle using the Java granted user.



CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Hello" AS
public class hello{
public static String world() {
return "hello world ";
}
};
/


Now verify the created Java method from the Java tab under schema browser.



Next, create a procedure/function to call the Java method. Remember, function should return one value but procedure don't.



CREATE OR REPLACE function Hello RETURN VARCHAR2
as LANGUAGE JAVA NAME 'hello.world() return String';
/


Finally, test it out with a simple SELECT FROM DUAL SQL.

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )