• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

cursor?

 
Sam Doder
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 204
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try this:

Tested on Oracle 11g2:

If you want to use typed refcursor, you can declare type and function within a package
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic