Common cursor for 2 PL/SQL functions

[Leslie Chaim]

Is it possible to have a common cursor or pre-parsed statement shared between two functions.
For example, I have this JDBC app which calls foo1 as:
select foo1(varA, varB)
from table
where some condition...
After processing the results my app continues:
select foo2(varC, varD)
from table
where some condition...
Within foo1 and foo2 there is one SQL cursor that is the same, and I am wondering if there's a way to have these functions share a common SQL. I was thinking packages but I do not have much experience with it, so I am asking for some guidance.
Thanks
Leslie
[ July 01, 2003: Message edited by: Leslie Chaim ]

[SJ Adnams]

sounds like you want to do a union? can you explain a little more what you are trying to do?

[Leslie Chaim]

Thanks Simon for your response.
These select stmts were just an idea. What I really want is the general topic: common cursor for 2 PL/SQL functions
Can it be done and how?
Thanks,
Leslie

[Mark Spritzler]

Good question not sure if this works. But if you use a package and these two procedures are in the package, then there are reference cursors, an cursors at the package level in which both procedures can use.
On the other hand, I am confused at when or where you would need such a thing. You can have one procedure call the other and pass the cursor, you might have to use a reference cursor here. My memory on what is exactly allowed is bad, but I can always look it up online or in any reference book.
I would rethink your purpose and realize that there is probably a better solution that having to use a cursor in two procedures.
For instance. Procedure 1 has the cursor. It loops through the records, and for each record you pass the values to the other procedure to do work. Or you combine both procedures into one procedure. Or you have a cursor in each one which performs the same query. I'd think that in most cases your query should be a simple quick query, so running it twice in two different procedures won't cost that much.
Mark

[Leslie Chaim]

My memory on what is exactly allowed is bad, but I can always look it up online or in any reference book.
Who said this expression: "If I would only remember what you had forgotten"
Thanks for you input Mark, I will rethink!
Passing around the cursor as an Object "sounds" the way to go.
Cheers,
Leslie