Home > Articles > Programming > Java

  • Print
  • + Share This
  • 💬 Discuss

This chapter is from the book

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>
  • + Share This
  • 🔖 Save To Your Account
Oracle and Java Development

This chapter is from the book

Oracle and Java Development

Discussions

comments powered by Disqus