15-7. Passing Parameters Between PL/SQL and Java
Problem
You want to pass parameters from PL/SQL to a Java stored procedure that expects them.
Solution
Create a call specification that accepts the same number of parameters as the number the Java stored procedure expects. For this example, an additional method will be added to the Employee Java class that was created in Recipe 15-1. This method will be an enhanced version of the original method that will accept a department ID as an input argument. It will then query the database for the employees who belong to that department and display them.
The following code is the enhanced Java method that will be added the Employees class contained within the Employees.java file:
public static void getItEmpsByDept(int departmentId){
String firstName = null;
String lastName = null;
String email = null;
try {
Connection conn = DriverManager.
getConnection("jdbc:default:connection:");
String sql = "SELECT FIRST_NAME, LAST_NAME, EMAIL " +
"FROM EMPLOYEES " +
"WHERE DEPARTMENT_ID = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, departmentId);
ResultSet rset = pstmt.executeQuery();
while(rset.next()) {
firstName = rset.getString(1);
lastName = rset.getString(2);
email = rset.getString(3);
System.out.println(firstName + " " + lastName + " " +
email);
}
pstmt.close();
rset.close();
} catch (SQLException ex){
System.err.println("ERROR: " + ex);
}
}
Once this method has been added to the Employees class, then the Java source should be loaded into the database using the technique demonstrated in Recipe 15-2.
Note You must include the OR REPLACE clause of the CREATE JAVA statement if the Employees source is already contained in the database. If you do not include this clause, then you will receive an Oracle error.
Once the Java has been loaded into the database and compiled, you will need to create the call specification that will be used by PL/SQL for accessing the Java stored procedure. The following code demonstrates a call specification that will accept a parameter when invoked and pass it to the Java stored procedure:
CREATE OR REPLACE PROCEDURE get_it_emps_by_dept(dept_id IN NUMBER)
AS LANGUAGE JAVA
NAME 'Employees.getItEmpsByDept(int)';
The procedure can now be called by passing a department ID value as such:
SQL> exec get_it_emps_by_dept(60);
Alexander Hunold AHUNOLD
Bruce Ernst BERNST
David Austin DAUSTIN
Valli Pataballa VPATABAL
Diana Lorentz DLORENTZ
PL/SQL procedure successfully completed.
How It Works
The call specification is what determines how a Java stored procedure or function's arguments are mapped to PL/SQL arguments. To implement parameters, the call specification must match each parameter in the stored procedure or function to an argument in the specification. As mentioned in previous recipes, the call specification is a PL/SQL procedure itself, and each argument that is coded in the specification matches an argument that is coded within the Java stored procedure.
The datatypes that Java uses do not match those used in PL/SQL. In fact, a translation must take place when passing parameters listed as a PL/SQL datatype to a Java stored procedure that accepts parameters as a Java datatype. If you are familiar enough with each of the two languages, the translation is fairly straightforward. However, there are always those cases where one is not sure what datatype to match against. Table 15-1 lists some of the most common datatypes and how they map between Java and PL/SQL. For a complete datatype map, please refer to the Oracle documentation at http://download.oracle.com/docs/cd/B28359_01/java.111/b31225/chsix.htm#CHDFACEE.
| SQL Datatype | Java Type |
CHAR |
oracle.sql.CHAR |
VARCHAR |
java.lang.String |
LONG |
java.lang.String |
NUMBER |
java.lang.Integer,Java.lang.Float,Java.lang.Double,Java. |
DATE |
oracle.sql.DATE |
TIMESTAMP |
oracle.sql.TIMESTAMP |
TIMESTAMP WITH TIME ZONE |
oracle.sql.TIMESTAMPTZ |
TIMESTAMP WITH LOCAL TIME ZONE |
oracle.sql.TIMESTAMPLTZ |
BLOB |
oracle.sql.BLOB |
CLOB |
oracle.sql.CLOB |
Creating a PL/SQL call specification that includes parameters must use the fully qualified Java class name when specifying the parameter datatypes in the Java class method signature. If an incorrect datatype is specified, then an exception will be thrown. For instance, if you want to pass a VARCHAR2 from PL/SQL to a Java stored procedure, the signature for the Java class method must accept an argument of type java.lang.String. The following pseudocode demonstrates this type of call specification:
CREATE OR REPLACE PROCEDURE procedure_name(value VARCHAR2)
AS LANGUAGE JAVA
NAME ‘JavaClass.javaMethod(java.lang.String)’;