Meaningless Drivel is fun!
The moose likes JDBC and Relational Databases and the fly likes Rahul's Argument !! Function/Procedure retrun Cursor itself to a Java Program?!! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Rahul Watch "Rahul New topic

Rahul's Argument !! Function/Procedure retrun Cursor itself to a Java Program?!!


Joined: May 10, 2001
Posts: 8
Dear Friends
In fact this code discusssion really created curiosity.
pisal rahul advises that u can fetch cursor to java program.
as follows. I would like to know then how to write a procedure
for that. I want fetch 3 feilds from a citymaster table and return to java Program . Can anyone help out. Rahul r u there somewhere near by
Rahul Argues as follows :
do the following
CallableStatement stmt = con.prepareCall
("{? = call procedurename(?)}");
ResultSet rs = (ResultSet)stmt.getObject(1);

Frank Carver

Joined: Jan 07, 1999
Posts: 6920
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at . We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please log in with a new name which meets the requirements.

Read about me at ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
Please see following example which I borrow now from one
of my previous assignments:
import java.sql.*;
import oracle.jdbc.driver.*;
public class retrefcur_samp
public static void main (String args [])
// throws SQLException
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
connection URL.
Connection conn =
DriverManager.getConnection ("jdbcracle:thin:@pavilion03:1529ra8i", "you", "me");
init (conn);
CallableStatement call =
conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");

call.registerOutParameter (1, OracleTypes.CURSOR);
call.setString (2, "*");
call.execute ();
OracleResultSet rset = (OracleResultSet)call.getObject (1);

while ( ()){
System.out.println (rset.getString ("JOB"));
} catch(SQLException esql) {
System.out.println("Trouble in river city ");
static void init (Connection conn)
throws SQLException
Statement stmt = conn.createStatement ();
stmt.execute ("create or replace package java_refcursor as " +
" type myrctype is ref cursor return EMP%ROWTYPE; " +
" function job_listing (j varchar2) return myrctype; " +
"end java_refcursor;");
stmt.execute ("create or replace package body java_refcursor as " +
" function job_listing (j varchar2) return myrctype is " +
" rc myrctype; " +
" begin " +
" open rc for select * from emp; " +
" return rc; " +
" end; " +
"end java_refcursor;");
stmt.execute ("create or replace package java_ref_clean as " +
" type myrctype is ref cursor; " +
" function cleanup (cc myrctype) return number; " +
"end java_ref_clean;");
stmt.execute ("create or replace package body java_ref_clean as " +
" function cleanup (cc myrctype) return number is "+
" result number; " +
" begin " +
" close cc; " +
" end; " +
"end java_ref_clean;");

andre van winssen
Abhilash Nair

Joined: Jun 13, 2001
Posts: 2
Dear Andre,
Thanks a lot for that advice. It is of really great help
mukesh kumar

Joined: Aug 30, 2001
Posts: 1
I saw Topic: Rahul's Argument !! Function/Procedure retrun Cursor itself to a Java Program?!!
I have similar problem. I am selecting 7 fields from 3 tables and creating a cursor in a function. It returns all rows in one step.
I want only 10 rows at a time (As in this topic getting all row at a time).
For this I have to fetch in loop for 10 times, but gives compilation error in oracle when I write fetch into ref_cursor.
Should I take all values in arrays or is there a way to modify andre van winssen's suggested way.
I agree. Here's the link:
subject: Rahul's Argument !! Function/Procedure retrun Cursor itself to a Java Program?!!
It's not a secret anymore!