How to return a JDBC ResultSet as Ref Cursor from a Java Stored Procedure

Date: 03-Mar-2003

After reading this how-to document you should be able to:

• Create a Java Stored Procedure which returns a JDBC Result set as REF CURSOR

• Create the call specification for a Java Stored Procedure

Introduction

This document demonstrates how to return a JDBC ResultSet as REF CURSOR from a Java Stored Procedure. JDBC ResultSet is a table of data representing a database, which is usually generated by executing a statement that queries the database. REF CURSOR is the corresponding type in PL/SQL. The call specification of the Java Stored Procedure maps ResultSet to REF CURSOR. Prior to Oracle9i, it was not possible to return a ResultSet directly from a Java Stored Procedure because there was no mapping defined of the form ResultSet->REF CURSOR. Oracle9i adds this mapping to permit returning ResultSet from a function or as an OUT parameter to some procedure. The reverse mapping (REF CURSOR->ResultSet) is still not supported, so IN and IN OUT parameters are still not supported with current version of the database.

In this How to, we have 2 Java Stored Procedures. The Java Stored procedure getEmployees() fetches all the columns of EMP table in the SCOTT schema into a ResultSet and return it. The Java Stored Procedure getDepartments(ResultSet[] rout) takes a ResultSet object as a OUT parameter and fetches all the columns of DEPT table into this ResultSetobject.

Software Requirements

• Oracle9i Database version 9.0.1 or later. You can download the Oracle9i database from Oracle Technology Network. 

• JDK1.2.x or above This can be downloaded from here .

Description

First the Java Stored Procedures has to be created. Below are the code snippets Click here to see the complete java code.

In the Java Stored Procedure, the default server side connection to the database is obtained. By default, statements created by the server-side JDBC driver produce ResultSet objects that cannot be converted to a REF CURSOR. To return a ResultSet as a REF CURSOR, you must create the Statement or PreparedStatement in a special manner, setCreateStatementAsRefCursur(true) must be called on the Connection prior to creating that statement. Failure to call setCreateStatementAsRefCursor(true) prior to creating and returning a ResultSet as a REF CURSOR will result in the following error message when the Java Stored Procedure is executed:

ORA-00932: inconsistent datatypes

Once setCreateStatementAsRefCursor(true) is called on the Connection object, all the ResultSet returned by query on this connection can be converted to REF CURSOR.

Java Stored Procedure Listing 1

public static ResultSet getEmployees() {
.............
.............
  // Obtain default connection
  Connection conn = new OracleDriver().defaultConnection();

  // Create any subsequent statements as a REF CURSOR 
  ((OracleConnection)conn).setCreateStatementAsRefCursor(true);

  // Create the statement
  Statement stmt = conn.createStatement();

  // Query all columns from the EMP table
  ResultSet rset = stmt.executeQuery("select * from emp");

  // Return the ResultSet (as a REF CURSOR)
  return rset;
...........
...........

public static void getDepartments( ResultSet[] rout ) {

............
............
  // Obtain the default connection
  Connection conn = new OracleDriver().defaultConnection();

  // Create any subsequent statements as a REF CURSOR
  ((OracleConnection)conn).setCreateStatementAsRefCursor(true);

  // Create the statement
  Statement stmt = conn.createStatement();

  // do a simple query
  ResultSet rset = stmt.executeQuery("select * from dept");

  // return the ResultSet (as a REF CURSOR)
  rout[0] = rset;
...........
...........

The above Java Stored Procedure getDepartments( ResultSet[] rout ) queries all the rows of the DEPT table and returns the ResultSet in the OUT parameter.

To see the working of the application, load the Java class into the SCOTT schema of your database.

>loadjava -thin -user scott/tiger@<hostname>:<port>:<SID> -resolve -verbose RefCursor.java

where,

For example,

>loadjava -thin -user scott/tiger@insn104a.idc.oracle.com:1521:otn9idb -resolve -verbose RefCursor.java

Then create the call specification for the Java Stored Procedure. Connect to the SCOTT/TIGER user and execute the following at the SQL prompt

create or replace package refcurpkg is
  type refcur_t is ref cursor;
end refcurpkg;
/

create or replace function getemps return refcurpkg.refcur_t is
language java name 'RefCursor.getEmployees() return java.sql.ResultSet'; 
/


create or replace procedure getdepts(cur OUT refcurpkg.refcur_t) is
language java name 'RefCursor.getDepartments(java.sql.ResultSet[])';
/

You can now test the Java Stored Procedure by executing the following in the SQL prompt

SQL>variable x refcursor
SQL>execute :x := getemps;
SQL>print x

This should print the contents of the EMP table in the SCOTT schema.

SQL>execute getdepts(:x);
SQL>print x

This should print the contents of the DEPT table in the SCOTT schema.

Resources

• Java Stored Procedure Samples are available at /sample_code/tech/java/jsp/index.html
• A white paper on developing Java Stored Procedure is available at /tech/java/jsp/pdf/stored_procs_in_java_twp.pdf

Summary

This document explained about how to create a Java Stored Procedure that returns a JDBC ResultSet as REF CURSOR and how to write the call specification for Java Stored Procedure.