D Using a PL/SQL Procedure With JDBC

This section provides an example of the use of a PL/SQL stored procedure with JDBC.

This section does not provide detailed information about Java and JDBC, or their use with Oracle Database Express Edition. For a brief summary of JDBC and links to resources for JDBC, see "Oracle Java Database Connectivity (JDBC)" .

This section contains the following topics:

JDBC and Oracle Database XE
Creating a Java Program That Calls a PL/SQL Procedure

JDBC and Oracle Database XE

Oracle Java Database Connectivity (JDBC) is an API that enables Java to send SQL statements to an object-relational database such as Oracle Database XE.

To run the Java program in Example D-1, you need to have Oracle Database XE and the full Java 2 Software Development Kit, Standard Edition (J2SE SDK), installed on your computer. Note that the Oracle Database XE Client is included in Oracle Database Express Edition.

Creating a Java Program That Calls a PL/SQL Procedure

Example D-1 shows the Java code that searches for and displays employee information based on the first and last names of an employee. The Java program accepts user input for the first and last names from command line arguments. The arguments can be the full names or substrings of the first and last names of the employee. When the program is executed, a connection is made to Oracle Database XE, the get_emp_info procedure is called to search for the employee records that match the input strings, and then the results are displayed.

The PL/SQL get_emp_info procedure is created in Example 5-15. The procedure determines the result set for the cursor variable (REF CURSOR) that is passed to the Java program. The package specification in Example 5-13 defines the cursor variable (my_refcur_typ) that is declared in the get_emp_info procedure. A cursor variable can be passed as a parameter to other packages, procedures, and functions. For information about cursor variables (REF CURSORs) see "Cursor Variables (REF CURSORs)". For information about using types in package specifications, see "Accessing Types in Packages".

The Java program in Example D-1 is intended only to be an illustration of the use of a PL/SQL stored procedure with JDBC. It does not include error checking or many other Java features.

Save the Java program in Example D-1 as EmpSearch.java. Before running the Java program in Example D-1, the PL/SQL get_emp_info procedure in Example 5-15 must be created by the HR user.

Example D-1 Creating a Java Program for Use With a PL/SQL Procedure

import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.*;

public class EmpSearch
{

  public static void main (String args[]) throws SQLException
  {
   // check whether there are two command-line arguments before proceeding
   if ( args.length < 2)
    {
     System.out.println("Enter both a first and last name as command-line arguments.");
     System.out.println("You can enter a complete name or an initial substring.");
     System.out.println("For example: java EmpSearch j doe");
     }
   else
     {
      // connect to a local XE database as user HR
      OracleDataSource ods = new OracleDataSource();
      ods.setURL("jdbc:oracle:thin:hr/hr@localhost:1521/XE");
      Connection conn = ods.getConnection();

      // call the PL/SQL procedures with the three parameters
      // the first two string parameters (1 and 2) are passed to the procedure
      // as command-line arguments
      // the REF CURSOR parameter (3) is returned from the procedure
      String jobquery = "begin get_emp_info(?, ?, ?); end;";
      CallableStatement callStmt = conn.prepareCall(jobquery);
      callStmt.registerOutParameter(3, OracleTypes.CURSOR);
      callStmt.setString(1, args[0]);
      callStmt.setString(2, args[1]);
      callStmt.execute();

      // return the result set
      ResultSet rset = (ResultSet)callStmt.getObject(3);

      // determine the number of columns in each row of the result set
      ResultSetMetaData rsetMeta = rset.getMetaData();
      int count = rsetMeta.getColumnCount();

      // print the results, all the columns in each row
      while (rset.next()) {
          String rsetRow = "";
          for (int i=1; i<=count; i++){
                 rsetRow = rsetRow + " " + rset.getString(i);
          }
          System.out.println(rsetRow);
       }

    }
  }
}

Before you attempt to compile the Java program, make sure the CLASSPATH has been set. For example, on a Windows computer you could use the following value for CLASSPATH, if Oracle Database XE is installed in the default location.

.;C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar;C:\oraclexe\app\oracle\
product\10.2.0\server\jlib\orai18n.jar;

Compile the EmpSearch.java Java program. For example, on a Windows computer you could use one of the following examples, depending on whether the path to javac has been set.

javac EmpSearch.java
c:\j2sdk1.4.2_04\bin\javac EmpSearch.java

Run the program with valid command-line arguments. For example, on a Windows computer you could use one of the following examples, depending on whether the path to java has been set. With the d and gr command-line arguments, the PL/SQL procedure searches for employees whose first name starts with d and last name starts with gr.

java EmpSearch d gr
c:\j2sdk1.4.2_04\bin\java EmpSearch d gr

Description of the illustration xe_jdbc.gif