title: Using roolback/commit colses all cursor, how to over come
body: <p>I have PL/SQL code, where in I am performing Insert into table 1 and based on some condition delete from table2, if delete succeeds then commit all above staments and insert in table3 with status 'cond_true' <strong>else</strong> rollback all above transaction and insert in table3 'cond_false' .  If used commit and rollback within this code, it will close the opened cursor and will have to re-iterate for each cursor again or fail with no cursor open status.</p>
  
  <pre><code>OPEN cursor1;       
      Loop            
          fetch cursor1 into tbls;
          exit when cursor1%notfound;                     
  
              execute immediate  INSERT INTO  table1  SELECT  * FROM table2; 
              -- commit only if below condition is true else rollback;
  
          BEGIN
              IF  some_condition THEN  
                 -- this condition will become true only after the above insert is executed successfully for all rows
                  execute immediate   DELETE FROM table2 where some_condition2;
  
                  INSERT INTO table3 values('contition_true');
                  -- commit;
              ELSE
                  INSERT INTO table3 values('contition_false'); 
                  --- first rollback the first insert and then commit only this statement;
              END IF;
          END;
      END LOOP;
  close cursor1;   
  </code></pre>