How to create a function that returns a cursor? 2

[DixieFlatline]

Hi,

Can anyone show me the syntax on how to create a function that would return a cursor?

What I want is to be able to call this function in jdbc like:

CallableStatement cs =
conn.prepareCall( "{ call myFunc[(?)]});
cs.setInt( 1 , 1 );
ResultSet rs = cs.executeQuery();

How do I form the function in Oracle so that I can do a jdbc call like the one above.

Thanks.

 

[stevecal]

What you need here is a ref cursor:
You need to declare these (either strong or weak) at the top of your package

TYPE xyz_tab_curtype IS REF CURSOR RETURN xyz_tab%ROWTYPE; -- strong
TYPE xyz_curtype is REF CURSOR; -- weak

Now you need a function for this (I'll use the weak cursor because I only want to pass back specific fields here)e.g.

Procedure Find(vName IN VARCHAR2, xCursor OUT xyz_curtype)
IS
cst xyz_curtype;
BEGIN

OPEN cst FOR
SELECT xyz.field1, xyzfield2
FROM xyz_tab xyz
WHERE
xyz.xyz_id IN
(SELECT a.abc_id FROM abc_tab a )
ORDER BY xyz.field2;

-- pass your local cursor to the OUT cursor (you could use -- the OUT cursor to start with but we have a standard that
-- says you do not directly reuse OUT parameters.
-- (this is for debugging reasons)

xCursor := cst;
return;
END Find;

et voila, one open cursor which you can reference and walk with your jdbc...

 

[DixieFlatline]

Thanks! I will give this a try.