Cursors

[Guest_imported]

Hi All,
I am new to this Forum.I have a problem.I have a package with two procedures.Procedure A and Procedure B.Procedure A calls Procedure B and passes a cursor(Cursor A).Procedure B has a cursor(Cursor B).There is Fetch loop in the Procedure B.After the loop execution is resumed back at Procedure A,but when I fetch Cursor A the cursor pointer is at the last record.Why does this happen.When I pass a cursor What happens?Is the value of the cursor passed or the reference of the cursor passed.If you have a solution to this please let me know.I am passing a REF cursor by the way.
Regards
Joseph

 

[sem]

REF means reference, so you have the same cursor in both procedures. There is nothing strange, that you may fetch it from the begining to the end for just 1 time. You may reopen cursor in procedure A or use 2 similar cursors.

 

[Guest_imported]

Hi sem Thanks for your answer.How do I reopen the cursor.Do I have to write the select statement again.
Regards
Joseph

 

[sem]

It depends on whether you've opened it for select as a string or it was declared as explicit cursor. In the 1st case you have, in the 2nd you may close it and open again.

 

[Kenrae]

Be careful since cursors are global variables. I suppose cursor A is different from cursor B, isn't it?

 

[Guest_imported]

Can I use the example without using ref cursor.What is the syntax for using it.
for eg
ProcedureA(a out ctype)
ProcedureB(b in ctype)
ProcedureA calls procedureB.So in this case does cursor a and b have the same memory location.If I dont use REF cursor then what will be the procedure syntax and will it help me out with my problem of not opening the cursor again.
ProcedureA(a out???).
Regards
Joseph

 

[sem]

If you need to loop through cursor and do something in both procedures, you may store the result set in some buffer, e.g. pl/sql table and loop through it by passing it to B procedure as a parameter or using package variable.