Jaime

• Jul 16th, 2005

 

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.  
 
Click here to Read Complete Tutorial

guptach

 Profile Answers by guptach 

• Sep 23rd, 2005

 

Stored Procedures can be called in java by using the prepareCall() method which returns a CallableStatement object.

CallableStatement cs = con.prepareCall("{call Procedure_name}");

ResultSet rs = cs.executeQuery();