cursor?

[Sam Doder]

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

  

FUNCTION results( a number ) return SYS_REFCURSOR as
cursor mycursor return TESTTABLE%T1 is select * from TESTTABLE ;
BEGIN

open mycursor ; -- for select * from TESTTABLE ;
return mycursor ;
END results;

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]

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]

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]

Try this:

DROP table testtable;

CREATE TABLE testtable
AS 
select table_name, tablespace_name
from user_tables;


create or replace
FUNCTION results( A NUMBER ) RETURN SYS_REFCURSOR AS
 mycursor sys_refcursor;
-- cursor mycursor return TESTTABLE%T1 is select * from TESTTABLE ;
BEGIN
  OPEN mycursor FOR SELECT * FROM TESTTABLE  ;
  RETURN mycursor ;
END results;
/

variable cur refcursor;
execute :cur := results( 2 );
print :cur

Tested on Oracle 11g2:

DROP table testtable succeeded.
CREATE TABLE succeeded.
FUNCTION RESULTS compiled
anonymous block completed

CUR
-----------------------------------------------------------------------
TABLE_NAME                     TABLESPACE_NAME                
------------------------------ ------------------------------ 
BT1                            USERS                          
BT                             USERS      

If you want to use typed refcursor, you can declare type and function within a package

CREATE OR REPLACE PACKAGE TEST AS
  TYPE sys_typed_refcursor IS REF CURSOR RETURN testtable%rowtype;
  FUNCTION results( A NUMBER ) RETURN sys_typed_refcursor;
END TEST;
/

create or replace
PACKAGE BODY TEST AS
  FUNCTION results( A NUMBER ) RETURN sys_typed_refcursor
  AS
     mycursor sys_typed_refcursor;
  BEGIN
    OPEN mycursor FOR SELECT * FROM TESTTABLE  ;
    RETURN mycursor ;
  END results;
END TEST;
/