Re: Reusing Cursor in PL/SQL

Chris wrote:

> I'm trying to come up with a solution that will reuse a cursor. Here's
> what I'm trying to accomplish:
>
> 1) query database for affected items and place into item_cursor
> 2) query datbase for affected items2 and place into item2_cursor
> -- item and item2 are from different tables
> 3) loop through item_cursor and run a procedure on each record that will
> eventually delete the record from the item table
> 4) loop through item2_cursor and run a procedure on each record that
> will eventually delete the record from the item2 table
> 5) add a new record for item2 (this replaces the one that was deleted in
> the previous step)
> 6) loop through affected items from item_cursor and run another
> proceedure that will add them back to the database with the new
> information that was added in step 5
>
> I tried to use FOR loops on each but I suspect that when I do the second
> loop for item_cursor it will bring back no records since the first loop
> deleted them. Is there a way to store the item_cursor so that the
> values are not deleted from memory during the delete step? In other
> words, I need to be able to loop through the records a second time so
> how can I store them so I can run through the records after deleting the
> data from the table?
>
> Thanks for your help,
> Chris
>

Is this homework?!?

- drop pl/sql, use sql
- you cannot "replace" a deleted item; a deleted item is gone.
- if you insist on pl/sql: lookup pl/sql tables, bulk bind, forall
-- 

Regards,
Frank van Bortel Received on Tue Jun 22 2004 - 12:52:15 CDT