15-11. Embedding a Java Class Into a PL/SQL Package
Problem
You are interested in creating a Java class and making each of its methods and attributes available to PL/SQL in an organized unit of code.
Solution
Use a PL/SQL package to declare each of the attributes and methods that reside within the Java class, and then create separate call specifications for each of the Java methods within the PL/SQL package body. The following code demonstrates the creation of a PL/SQL package named EMP_PKG, which declares each of the methods that reside within the Employee Java class and makes them available to PL/SQL via call specifications that are implemented within the package body.
First, create the package header as follows:
CREATE OR REPLACE PACKAGE EMP_PKG AS
PROCEDURE get_it_emps;
PROCEDURE get_it_emps_by_dept(dept_id IN NUMBER);
PROCEDURE emp_audit(emp_id NUMBER);
FUNCTION get_emp_job_title(emp_id IN NUMBER) RETURN VARCHAR2;
FUNCTION get_emp_department (emp Employee) RETURN VARCHAR2;
END;
Next, create the package body as follows, adding a call specification for each Java method or attribute you plan to use:
CREATE PACKAGE BODY EMP_PKG AS
PROCEDURE get_it_emps
AS LANGUAGE JAVA
NAME 'Employees.getItEmps()';
PROCEDURE get_it_emps_by_dept(dept_id IN NUMBER)
AS LANGUAGE JAVA
NAME 'Employees.getItEmpsByDept(int)';
PROCEDURE emp_audit(emp_id NUMBER)
AS LANGUAGE JAVA
NAME 'Employees.employeeAudit(int)';
FUNCTION get_emp_job_title(emp_id IN NUMBER) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'Employees.getEmpJobTitle(int) return String';
FUNCTION get_emp_department (emp Employee) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'Employees.getEmpDepartment(oracle.sql.STRUCT) return java.lang.String';
END;
Now the package can be used to call each of the underlying Java stored procedures instead of having separate PL/SQL procedures and functions for each. The following anonymous block has been modified to make use of the PL/SQL package for calling GET_EMP_DEPARTMENT rather than a stand-alone function.
DECLARE
CURSOR emp_cur IS
SELECT * FROM EMPLOYEES;
emp_rec emp_cur%ROWTYPE;
emp Employee;
BEGIN
FOR emp_rec IN emp_cur LOOP
emp := Employee(emp_rec.employee_id,
emp_rec.first_name,
emp_rec.last_name,
emp_rec.email,
emp_rec.job_id,
emp_rec.department_id);
DBMS_OUTPUT.PUT_LINE(emp.first || ' ' || emp.last || ' - ' ||
emp_pkg.get_emp_department(emp));
END LOOP;
END;
How It Works
In programming, it is a best practice to organize code in a way that makes it easy to maintain. Placing related procedures and functions inside a single PL/SQL package is one such application of that approach. The same can be said for working with Java code in the database. A few Java stored procedures or functions will not cause much trouble to maintain. However, once you start to accumulate more than a handful within the same underlying Java class, then it is a good idea to consolidate the call specifications into a single PL/SQL package.
In the solution to this recipe, all the Java stored procedures that are contained within the Employees Java class have call specifications that are grouped into a single PL/SQL package. If you create one PL/SQL package containing call specifications per each Java class that is loaded into the database, you will have a nicely organized environment. In some cases, you may have more than one Java class that contains the implementations that are to be used within a single PL/SQL application. In those cases, it may make more sense to combine all call specifications into a single PL/SQL package.
Using PL/SQL package to group call specifications is a good idea. Not only will this technique make for easier maintenance, but it also makes for more uniform applications with consistent interfaces.