Contains information about Teradata,BTEQ,FASTLOAD,FASTEXPORT, Oracle Pl/SQL,forms, JAVA open source related topics and discussions
Oracle forms and reports
  Oracle database 

16 May 2009

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

2 comments:

  1. Hi Kaushik,
    It's really useful, well done, keep it up.

    Regards,
    Shaw.

    ReplyDelete
  2. It is very helpful to us and i have learned,how to call java in oracle,
    tanks a lot ,

    ReplyDelete