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 cursor? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "cursor?" Watch "cursor?" New topic


Sam Doder
Ranch Hand

Joined: Feb 06, 2008
Posts: 204

This is a continued question for this thread thread

I am trying to create a function not a procedure that returns cursor.
I have tried this but get compiler errors

tried everything , basically what is the syntax for returning a cursor from a function.

Also is their any difference in functionality in using a stored procedure instead of a stored function. They both can be used to return values their as in/out parameters or just a return statement (same effect)

And you can have functions and procedures in the same package so why the distinction???

David Newton

Joined: Sep 29, 2008
Posts: 12617

The difference(s) between functions and procedures depends at least somewhat on what DB you're using. It looks like you're using Oracle; if so, see here:
Sam Doder
Ranch Hand

Joined: Feb 06, 2008
Posts: 204
Ok , other then a few facts functions and procedures can be used interchangeable.
Functions return values , procedures don't but you can get the same effect by using out , or in/out parameters.
Their are a few other distinctions but really alot of things can be written equivalent in procedures as they are in functions...etc

My problem is I am trying to figure out how I can create the equivalent FUNCTION for the Procedure example given in that thread.

Basically I want to return a result set to java using a CallableStatement that calls a stored function not a stored procedure.
And have that function return a cursor.

I am trying to return a cursor or ref cursor and not use in/out parameters that is the main goal.

I have tried using the return CURSOR , or return SYS_REFCURSOR but in the code I keep getting compiler errors for my stored function?

Thanks for an help
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Try this:

Tested on Oracle 11g2:

If you want to use typed refcursor, you can declare type and function within a package
I agree. Here's the link:
subject: cursor?
It's not a secret anymore!