- Overview of JDBC—Java Calling SQL and PL/SQL
- Fundamental Steps in JDBC
- INSERT, UPDATE, and DELETE Operations Using JDBC
- Oracle JDBC Extensions
- Calling Stored Procedures in JDBC Programs
- Handling Resultsets in JDBC
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Calling Stored Procedures in JDBC Programs
Stored procedures are part and parcel of any database application that enables a lot of business logic to be stored as application logic in the database in compiled form. Oracle 8i supports two types of stored procedures, namely, PL/SQL and Java. This section highlights the calling of both PL/SQL stored procedures and Java stored procedures from JDBC programs.
Calling PL/SQL Stored Procedures
PL/SQL stored procedures are called from within JDBC programs by means of the prepareCall() method of the Connection object created above. A call to this method takes variable bind parameters as input parameters as well as output variables and creates an object instance of the CallableStatement class.
The following line of code illustrates this:
CallableStatement stproc_stmt = conn.prepareCall
("{call procname(?,?,?)}");
Here conn is an instance of the Connection class.
The input parameters are bound to this object instance using the setXXX() methods on the CallableStatement object. For each input bind parameter, a setXXX() method (e.g., setInt(), setString(),) is called. The following line of code illustrates this:
stproc_stmt.setXXX(...)
The output parameters are bound to this object instance using registerOutParameter() method on the CallableStatement object, as shown below:
stproc_stmt.registerOutParameter(2, OracleTypes.CHAR);
The above statement registers the second parameter passed to the stored procedure as an OUT parameter of type CHAR. For each OUT parameter, a registerOutParameter() method is called.
Once a CallableStatement object has been constructed, the next step is to execute the associated stored procedure or function. This is done by using the executeUpdate() method of the CallableStatement object. The following line of code illustrates this using the stproc_stmt object created above:
stproc_stmt.executeUpdate();
prepareCall() Method
The three different kinds of stored sub-programs, namely, stored procedures, stored functions, and packaged procedures and functions can be called using the prepareCall() method of the CallableStatement object.
The syntax for calling stored functions is as follows:
CallableStatement stproc_stmt = conn.prepareCall
("{ ? = call _funcname(?,?,?)}");
The first ? refers to the return value of the function and is also to be registered as an OUT parameter.
Packaged Procedures and Functions
Packaged procedures and functions can be called in the same manner as stored procedures or functions except that the name of the package followed a dot "." prefixes the name of the procedure or function.
Once the stored procedure or function has been executed, the values of the out parameters can be obtained using the getXXX() methods (for example, getInt() and getString()) on the CallableStatement object. This is shown below:
String op1 stproc_stmt.getString(2);
This retrieves the value returned by the second parameter (which is an OUT parameter of the corresponding PL/SQL stored procedure being called and has been registered as an OUT parameter in the JDBC program) into the Java String variable op1.
A complete example is shown below. Consider a procedure that returns the highest paid employee in a particular department. Specifically, this procedure takes a deptno as input and returns empno, ename, and sal in the form of three out parameters.
The procedure is created as follows:
CREATE OR REPLACE PROCEDURE p_highest_paid_emp
(ip_deptno NUMBER,
op_empno OUT NUMBER,
op_ename OUT VARCHAR2,
op_sal OUT NUMBER)
IS
v_empno NUMBER;
v_ename VARCHAR2(20);
v_sal NUMBER;
BEGIN
SELECT empno, ename, sal
INTO v_empno, v_ename, v_sal
FROM emp e1
WHERE sal = (SELECT MAX(e2.sal)
FROM emp e2
WHERE e2.deptno = e1.deptno
AND e2.deptno = ip_deptno)
AND deptno = ip_deptno;
op_empno := v_empno;
op_ename := v_ename;
op_sal := v_sal;
END;
/
Here we assume that there is only one highest paid employee in a particular department.
Next we write the JDBC program that calls this procedure. This is shown below:
import java.sql.*;
public class StProcExample {
public static void main(String[] args)
throws SQLException {
int ret_code;
Connection conn = null;
try {
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection
conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
Oracle", "oratest", "oratest");
int i_deptno = 10;
CallableStatement pstmt = conn.prepareCall("{call p_highest_
paid_emp(?,?,?,?)}");
pstmt.setInt(1, i_deptno);
pstmt.registerOutParameter(2, Types.INTEGER);
pstmt.registerOutParameter(3, Types.VARCHAR);
pstmt.registerOutParameter(4, Types.FLOAT);
pstmt.executeUpdate();
int o_empno = pstmt.getInt(2);
String o_ename = pstmt.getString(3);
float o_sal = pstmt.getFloat(4);
System.out.print("The highest paid employee in dept "
+i_deptno+" is: "+o_empno+" "+o_ename+" "+o_sal);
pstmt.close();
conn.close();
} catch (SQLException e) {ret_code = e.getErrorCode();
System.err.println(ret_code + e.getMessage()); conn.close();}
}
}
Calling Java Stored Procedures
Java stored procedures can also be called from JDBC programs using the corresponding call specifications created to publish the Java methods into the Oracle 8i database. In other words, calling the published call specs executes the corresponding Java methods and the syntax for calling these is the same as calling PL/SQL stored procedures.
Here we will use the Java stored procedures created in Chapter 2, "Java Stored Procedures." The following JDBC program calls the packaged procedure pkg_empmaster.fire_emp (a Java stored procedure that corresponds to the Java method empMaster.fireEmp()). Specifically it deletes the record in emp table where empno = 1002.
Before executing the above Java stored procedure, the record corresponding to empno 1002 in emp table is as follows:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 1002 DAVID ANALYST 1001 01-JAN-01 6000 1000 10
The JDBC program to call the Java stored procedure is as follows:
import java.sql.*;
public class JavaProcExample {
public static void main(String[] args)
throws SQLException {
int ret_code;
Connection conn = null;
try {
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection
conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
Oracle", "oratest", "oratest");
int i_empno = 1002;
CallableStatement pstmt =
conn.prepareCall("{call pkg_empmaster.fire_emp(?)}");
pstmt.setInt(1, i_empno);
pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {ret_code = e.getErrorCode();
System.err.println(ret_code + e.getMessage()); conn.close();}
}
}
The output of the above program can be verified as follows:
SQL> select * from emp where empno = 1002; no rows selected SQL>