File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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

Win a copy of REST with Spring (video course) this week in the Spring forum!
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!