aspose file tools*
The moose likes JDBC and the fly likes cursor? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "cursor?" Watch "cursor?" New topic
Author

cursor?

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
Author
Rancher

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:

http://it.toolbox.com/blogs/oracle-guide/learn-plsql-procedures-and-functions-13030
http://www.orafaq.com/wiki/PL/SQL_FAQ#What_is_the_difference_between_stored_procedures_and_functions.3F
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: http://aspose.com/file-tools
 
subject: cursor?