Call Java Oracle Functions From Java program

Beginning with version 8i, the Oracle database includes a fully functional Java Virtual Machine, Oracle JVM. Out of this close relationship between Oracle and Java emerges an important technology for Oracle developers: Java stored procedures. With Java stored procedures, developers have the ability to harness the power of Java when building database applications. This article provides an overview of this increasingly popular technology. Its intended audience is Java developers new to Oracle, and Oracle PL/SQL developers with a basic understanding of Java.

also read:

Java Tutorials

Java EE Tutorials

Design Patterns Tutorials

Java File IO Tutorials

There are a number of scenarios where it makes sense to use Java stored procedures. Given Java’s popularity today, it is certainly possible that members of a development team are more proficient in Java than PL/SQL. Java stored procedures give Java programmers the ability to code in their preferred language. For experienced PL/SQL developers, using Java allows you to take advantage of the Java language to extend the functionality of database applications. Also, Java makes it possible to write database-neutral code. Better yet, it allows you to reuse existing code and dramatically increase productivity.

The better your understanding of Java stored procedures, the easier it will be to decide how they best fit your development practices. A common approach is to use PL/SQL when writing programs primarily concerned with database access. Then, as requirements arise that are more easily satisfied by Java, classes can be developed, followed by the necessary call specifications.

Perhaps, for instance, that a database application needs to interact with operating system files and directories. Oracle provides limited functionality with the UTL_FILE package for accessing system files. However, Java has a far richer set of File IO capabilities, allowing developers to remove files, add directories, and so on. So, why not leverage this power? The user of a command-line PL/SQL program might want to place job parameters in a configuration file. You could write a Java method to read these parameters.

Example: To run the example you will need the Sun JDK and Oracle 8i or better.

The steps involved are:-

1) Create a Java class containing your stored procedure/function.

 --DROP JAVA SOURCE MyUser."TestFunction";
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED MyUser."TestFunction" as
package util.oracle;
public class TestFunction {
       public static String testcall(String input) {
             return "prashant" + input;
      }
     } ;

2) Grant permissions to execute java source.

   GRANT EXECUTE ON JAVA SOURCE MyUser."TestFunction" TO My_User;

3) Compile the class outside of the database using a Java compiler.(Optional) This step will be better and required just to ensure java code is running properly.

4) Create a oracle package having a oracle function

CREATE OR REPLACE PACKAGE MyUser.TESTFUNCTION AS FUNCTION testcall(Param1 VARCHAR2) return VARCHAR2 ;
end testfunction;

Now run the script below where it contain body for the package declared.

CREATE OR REPLACE PACKAGE body MyUser.TESTFUNCTION AS FUNCTION testcall(Param1 VARCHAR2)
return VARCHAR2 AS LANGUAGE java NAME 'util.oracle.TestFunction.testcall(java.lang.String)
return java.lang.String'; end TESTFUNCTION; /

Please do execute the package along with functions.

5) Now create a java client and run the program.Make sure you have classes12.zip in classpath.

      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:dev4","dba","dba");
      System.out.println("Connection created..............");
      String call = "{ ? = call MyUser.TESTFUNCTION.testcall(?) }";
      CallableStatement cstmt = con.prepareCall(call);
      cstmt.setQueryTimeout(1800);
      cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.setString(2, "hello.......");
      cstmt.executeUpdate();
      String val = cstmt.getString(1);
      cstmt.close();
      con.close();
      System.out.println(val);
      Prints Connection created.............. prashanthello.......

Call Java Oracle Functions From Java program

About Krishna Srinivasan