This week's giveaway is in the Spring forum.
We're giving away four copies of REST with Spring (video course) and have Eugen Paraschiv on-line!
See this thread for details.
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 REST with Spring (video course) this week in the Spring 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!