Java Stored Procedure in Oracle

Though we can write business logic, data validation, exception handling in pl/sql, still we need to use languages like C, Pro*C, Java for complex and system programming in Oracle.

Recently we had a requirement to convert the Images and store it into Oracle. As we know pl/sql won’t support graphics I used Java program for image conversion and used it in my plsql.

Java program should have public method with required parameters and return statement if needed. For example, let as take addition of two numbers. Two numbers should be input parameters and the method should return sum of these two.

Compile the java file and keep the class file in folder/directory registered in Oracle Database. We can create new directory if needed using following syntax.

CREATE DIRECTORY Bfile_dir AS '/usr/temp';

Store the class file into database using following syntax.

CREATE OR REPLACE JAVA CLASS USING BFILE (Bfile_dir, 'Addition.class');

loadjava executable can also be used instead of the above statement. Now java class is available to use. Create function/package to use in pl/sql.

CREATE OR REPLACE FUNCTION add_numbers(no1 NUMBER, no2 NUMBER) RETURN NUMBER AS
LANGUAGE JAVA NAME 'Addition.get(int,int) return int';

I used Oracle 10g Release 2. Refer Oracle Database Java Developer’s Guide for more details.