Calling Java procedures from ORACLE PL/SQL

Why we need to call JAVA procedures in PL/SQL?  

JAVA one of the oldest and type safe language ever seen.It was first introduced into the IT world when C++ was not able to call remote procedures.

Almost there are thousands and thousands of logic from Hello world to complex logic fo aircrafts written in JAVA.So it is important to reuse the logic than rewriting from scratch in PL/SQL.

I have summarized the steps below to call a JAVA method from ORACLE 

Note

   Only STATIC methods can only be called  from PL/SQL as it as Modular language

Steps

1. Create a JAVA class to be loaded into Oracle database.

     Iam using our very old hello world program.Instead of printing Hello World to console, it would return the value to the user.

public class Hello 
       {   
         public static String world()   
         {     
           return "Hello world";   
          } 
        }

2. Compile the JAVA file using JAVAC

     Note

           Kindly add ORACLE_HOME/JDK/BIN in your PATH variable

Microsoft Windows XP [Version 5.1.2600] 
(C) Copyright 1985-2001 Microsoft Corp.  
C:\Documents and Settings\Administrator>javac H:\JDEV\Hello.java  

     

3. Load the JAVA files into Oracle database          

Note

   You should have added the path of ORACLE_HOME\JDK in the classpath or

it would show the driver not found error is thrown

C:\Documents and Settings\Administrator>loadjava -user store/store@shanthaguru H:\jdev\Hello.class  
C:\Documents and Settings\Administrator>  

Note

     After loading it into database , you can verify it using SQL*plus

   or SQL Developer

  

SQL * PLUS

SQL*Plus: Release 10.1.0.2.0 - Production on Sun May 17 13:07:14 2009  
Copyright (c) 1982, 2004, Oracle.  All rights reserved.   
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 
Production With the Partitioning, OLAP and Data Mining options  
SQL>  select object_name from user_objects where object_type = 'JAVA CLASS';  
OBJECT_NAME 
-------------------------------------------------------------------------------- 
Hello 

SQL DEVELOPER

    Open the JAVA node under the corresponding schema and see the loaded

JAVA Classes

   

4. Creating a PL/SQL procedure for calling the loaded the JAVA method   

SQL>  CREATE OR REPLACE FUNCTION helloworld RETURN VARCHAR2 AS   
2   LANGUAGE JAVA NAME 'Hello.world () return java.lang.String';   
3   /  Function created.

Note

   The return type should be JAVA specfic , note this in the above code.

Now try call the PL/SQL function to call the JAVA method

SQL> select helloworld from dual;  
HELLOWORLD 
-------------------------------------------------------------------- 
Hello world