Java Stored Procedure in Oracle, Database Interaction

Making Java classes available to Oracle can also be called “publishing Java classes.”  Any class that will be directly called from SQL or PL/SQL must be published. Now we shall proceed with publishing. 

A Java class is published by creating and compiling a call specification for it. The call specification is often referred to as a call spec or even a PL/SQL wrapper. It 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 addEmp (empno NUMBER,ename VARCHAR2,
  sal NUMBER, deptno NUMBER)
  AS LANGUAGE JAVA
  NAME 'Employee.addEmp(int, java.lang.String,
                          float, int)';
/

The “addEmp” procedure provides an SQL interface to the Java “Employee.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.

One should make sure that Java methods with “no return” values are wrapped as procedures, and those with return values are wrapped as functions. Consider a second Java method in “Employee” 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 deptno) {

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

   String sql = "SELECT COUNT(1) FROM emp WHERE deptno = ?";
   int c = 0; //containing the count
 .
 .
 .
 
   return c;
}

Its call specification specifies that it returns a NUMBER.

CREATE OR REPLACE FUNCTION getEmpCountByDept (deptno NUMBER)
   RETURN NUMBER AS LANGUAGE JAVA
   NAME 'Employee.getEmpCountByDept(int) return int';
/

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