How to return JDBC resultset as REF cursor from the Java stored procedure

How to return JDBC resultset as REF cursor from the Java stored procedure

Date: January 1, March 3, 2003

After reading this document, you should be able:

• Creates a Java stored procedure that uses the JDBC result set as the returned result of ref cursor.

• Call specifications for creating Java stored procedures

Introduction

This document demonstrates howJDBC ResultSetAsREF CURSOR.JDBC ResultSetIs a data table that represents a database. It is usually generated by executing a database query statement.REF CURSORYesPL/SQL. The call specification of the Java stored procedure maps the resultsetREF CURSOR. In oracle9IPreviously, it was impossible to directly returnResultSetBecause no form is definedResultSet->REF CURSOR. Oracle9IAdded this ing to allow return from the functionResultSetOr upload it as an out parameter to a process. However, it still does not support reverse ing (REF CURSOR->ResultSet), So the current version of the database still does not support the in and in out parameters.

In this method guide, we have two Java stored procedures. Java stored proceduregetEmployees()SetSCOTTIn ModeEMPMount all columns in the tableResultSetAnd return it. Java stored proceduregetDepartments(ResultSet[] rout)SetResultSetObject as the out parameter andDEPTAll columns in the table are mounted to thisResultSetObject.

Software requirements

• Oracle9IDatabase Version 9.0.1 or later. You can download oracle9 from Oracle technical networkIDatabase.

• JDK 1.2.x or later. You can download it from here.

Description

First, you must create a Java stored procedure. The following is a code snippet. Click here to view the complete Java code.

In the Java stored procedure, you can obtain the default server-side connection to the database. By default, statements created by the server-side JDBC driver cannot be convertedREF CURSOROfResultSetObject. Use resultsetREF CURSORReturned, must be created in a special wayStatementOrPreparedStatementBefore creating the statement, you mustConnection CallsetCreateStatementAsRefCursur(true). If you cannot create a resultset and use itREF CURSORCall before returningsetCreateStatementAsRefCursor(true), The following error message is displayed when the Java stored procedure is executed:

The ORA-00932: inconsistent PES ypes.

OnceConnectionThe object has been called.setCreateStatementAsRefCursor(true), All returned by the query for this connectionResultSetCan be convertedREF CURSOR.

Java stored procedure list 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 statementStatement stmt = conn.createStatement();// Query all columns from the EMP tableResultSet rset = stmt.executeQuery("select * from emp");// Return the ResultSet (as a REF CURSOR) return rset; ........... ...........

 

In the preceding Java stored proceduresetCreateStatementAsRefCursor(true)By default, the server connection is set to return the resultset that can be converted to ref cursor. Then QueryEMPAll rows in the table and return resultset.

Java stored procedure list 2

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 proceduregetDepartments( ResultSet[] rout )QueryDEPTAll rows in the table are returned in the out parameter.ResultSet.

To view the application running, load the Java class to your databaseSCOTTMode.

> Loadjava-thin-user Scott/tiger @

Where:

<Hostname>	Host Name for Database Installation
<Port>	Database TNS Listener Port
<Sid>	Database Name

For example:

> Loadjava-thin-user Scott/tiger@insn104a.idc.oracle.com: 1521: otn9idb-resolve-verbose refcursor. Java

Create a Java stored procedure call specification. ConnectSCOTT/TIGER Run the following code at the SQL prompt:

create or replace package refcurpkg istype refcur_t is ref cursor;end refcurpkg;/create or replace function getemps return refcurpkg.refcur_t islanguage java name 'RefCursor.getEmployees() return java.sql.ResultSet'; /create or replace procedure getdepts(cur OUT refcurpkg.refcur_t) islanguage java name 'RefCursor.getDepartments(java.sql.ResultSet[])';/

In the preceding listrefcurpkgCreates a new typeREF CURSORType. Create a Java stored procedure call specification. ReturnREF CURSORCall SpecificationgetemployeesPublish a Java stored proceduregetEmployees()。Stored Procedure by JavagetEmployees()ReturnedResultSetMapREF CURSOR.

Call SpecificationgetdeptsPublish a Java stored proceduregetDepartmentsAnd set the out ParameterREF CURSORMapResultSet

Now you can test the Java stored procedure by executing the following code at the SQL prompt:

SQL> variable X refcursor
SQL> execute: = getemps;
SQL> Print x

This operation will printSCOTTIn ModeEMPTable content.

 

SQL> execute getdepts (: X );
SQL> Print x

This operation will printSCOTT In ModeDEPTTable content.

Resources

• The Java stored procedure example can be obtained at/global/CN/sample_code/Tech/Java/JSP/index.html.
• The White Paper on developing Java stored procedures can be obtained at/Tech/Java/JSP/pdf/stored_procs_in_java_twp.pdf.

Summary

This document explains how to createJDBC ResultSetAsREF CURSORThe returned Java stored procedure, and how to write calling specifications for the Java stored procedure.