October 30, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Oracle and Java Stored Procedures

  • April 8, 2004
  • By Michael Klaene
  • Send Email »
  • More Articles »
#3. Publishing Classes

The third step in this process is to publish the Java class. Any class that will be directly called from SQL or PL/SQL must be published. A Java class is published by creating and compiling a call specification for it. The call specification, often referred to as a call spec or even a PL/SQL wrapper, maps a Java method's parameters and return type to Oracle SQL types. Here's the call specification for the addEmp method:

CREATE OR REPLACE PROCEDURE add_emp (emp_id NUMBER,emp_f_name VARCHAR2,
  emp_l_name VARCHAR2, emp_salary NUMBER, dept_id NUMBER)
  AS LANGUAGE JAVA
  NAME 'EmpManager.addEmp(int, java.lang.String, java.lang.String,
                          float, int)';
/

The add_emp procedure provides an SQL interface to the Java EmpManager.addEmp method. The Java method must be fully specified, including package name if relevant. Also, when developing a call specification, Java objects such as String must be fully qualified.

As a rule of thumb, Java methods with no return value are wrapped as procedures and those with return values become functions. Consider a second Java method in EmpManager that obtains a count of employees for a given department:

//Get the total number of employees for a given department.
public static int getEmpCountByDept(int dept_id) {

   Connection conn =
      DriverManager.getConnection("jdbc:default:connection:");

   String sql = "SELECT COUNT(1) FROM emp WHERE dept_id = ?";
   int cnt = 0;

   //Code here to add ResultSet value to cnt, trap SQLException, etc.

   return cnt;
} 

Its call specification specifies that it returns a NUMBER.

CREATE OR REPLACE FUNCTION get_emp_count_by_dept (dept_id NUMBER)
   RETURN NUMBER AS LANGUAGE JAVA
   NAME 'EmpManager.getEmpCountByDept(int) return int';
/

By default, as with standard PL/SQL procedures, these program units execute with INVOKER rights. In other words, they execute with the privileges of the current user. By adding the keywords AUTHID DEFINER, you allow others to execute with the privileges of the creator.

Once executed, call specifications join the other files in the database as members of the SCOTT schema.

#4. Calling the Procedures

We have developed, loaded, and published our Java classes. The final step is to execute them. By default, Java output is written to trace files. The DBMS_JAVA package, an Oracle-supplied package with utilities for managing server-side Java, has a method for redirecting output to SQL*Plus.

SQL> SET SERVEROUTPUT ON
SQL> CALL dbms_java.set_output(2000);

Now, Java output will displayed upon execution.

SQL> EXECUTE add_emp(1,'Joe', 'Smith',40000.00,1);
Creating new employee...

PL/SQL procedure successfully completed.

As you can see, from the caller's perspective, there is no discernable difference between calls made to Java stored procedures and calls to a PL/SQL procedure or function.

VARIABLE x NUMBER;
CALL get_emp_count_by_dept(1) INTO :x;
Getting Number of Employees for Dept...

Call completed.

PRINT x

         X
----------
         1

The SQLException class has the getErrorCode() and getErrorMessage() methods to help report errors. Any uncaught exception in a Java Stored Procedure results in an 'ORA-29532 Java call terminated by uncaught Java exception' for the caller. How you choose to handle errors will vary by application. The addEmp method simply catches and displays the error. We receive an error message when we attempt to add an emp record with an invalid dept_id.

SQL> execute add_emp(2,'Tom', 'Jackson', 45000.00,2);
Creating new employee...
ERROR! Adding Employee : ORA-02291: integrity constraint
                         (OPS$AK4353.FK_DEPT_ID) violated -
                         parent key not found

Because there is a need to call Java from PL/SQL, it is reasonable to assume that we will also need a way to call PL/SQL from Java code. This is very easy to achieve by using a CallableStatement object in our Java methods.

CallableStatement cstmt = conn.prepareCall("{my_plsql_proc}");

Thus, it is possible to create a seamless environment of PL/SQL procedures calling Java and vice versa.

A Usage Scenario

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.

   public static String readFile (String usrFile)  {
     String fileStr = new String();
     try {
        File file = new File(usrFile);
        FileReader fr = new FileReader(file);
        LineNumberReader lnr = new LineNumberReader(fr);
        ...
        ...
     }
     catch(Exception e) {
        ...
     }
     return fileStr;
   }

Then, a PL/SQL package will define call specifications for this and any other FILE IO method you choose to write.

CREATE OR REPLACE PACKAGE my_java_utils IS

FUNCTION read_file (file VARCHAR2) RETURN VARCHAR2;

END my_java_utils;
/

CREATE OR REPLACE PACKAGE BODY my_java_utils IS

FUNCTION read_file (file VARCHAR2) RETURN VARCHAR2
  AS LANGUAGE JAVA
  NAME 'MyJavaUtils.readFile(java.lang.String) return java.lang.String';

END my_java_utils;
/

A PL/SQL procedure can invoke the Java stored procedure read_file and use a file's data as input. Using this best of both worlds approach, developers can develop a robust database application. It should be noted that in this particular scenario, certain permissions may be required to access the file system. Developers should consult Oracle's Java security documentation for further information.

SUMMARY

Oracle provides a good deal of documentation on Java stored procedure development. If desired, you can download the code samples accompanying this article and experiment with them in greater detail.

In this article, I've given you an overview of Java Stored Procedures and demonstrated how to implement them. Java stored procedures is a powerful technology, worthy of an Oracle developer's consideration when building database applications.

Downloads

Download the accompanying source code here.

About the Author

Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel