This content has been marked as final. Show 4 replies
If you open a cursor and loop on it, and after the loop is finished, then loop on it again (without closing the cursor in between), ...That is not possible, so the rest of the question is pretty meaningless.
Use BULK Collect
OPEN cur_test; FETCH cur_test BULK COLLECT INTO tbl_test; CLOSE cur_test; FOR i in 1..tbl_test.COUNT LOOP Do Something for first time END LOOP; FOR i in 1..tbl_test.COUNT LOOP Do Something for second time END LOOP;
Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables.
Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results will be the contents of the changed table(s) when everything is finished.
Always say which version of Oracle you're using.
It's unclear why you need two cursors. You can probably do everything you want to in a single MERGE statement, especially if you're using Oracle 11, where MERGE can DELETE rows.
Read the following article on Ref Cursors... the principle is the same...