2 Replies Latest reply: Apr 9, 2013 3:13 PM by 1002056 RSS

    Cursor and Transactions problem

    1002056
      Hi friends,

      I'm executing the below set of commands where certain records are to be deleted as a transaction. If there is any problem with a transaction set , that set of records is skipped and next set of records is executed. With below code, if any error occurs in any of the statement, the control passes to the exception block from where it rolls back to the savepoint and unwanted statements are rolled back. so far so good. But the problem is execution stops here. I need to skip that record id and execute loop for remaing IDs. Please help.

      create or replace PROCEDURE DeleteRecords
      AS
      SelectDate date:= SysDate()-27;
      TRNID varchar2(100);
      CURSOR c_TrnInfoNull IS select id from trn_info where CREATED_TISTMP<SelectDate and trn_tistmp is null;
      BEGIN

      FOR myrows IN c_TrnInfoNull LOOP
      SAVEPOINT TrnSavePointNull;
      delete from TRN_ERROR_DETAIL where TRN_INFO_ID=myrows.id;
      select id into TRNID from TRN_PYMT_DETAIL where trn_info_id=myrows.id;
      delete from TRN_PYMT_ADDNL_DATA where TRN_PYMT_DETAIL_ID=TRNID;
      delete from TRN_PYMT_DETAIL where ID=TRNID;
      delete from trn_info where id=myrows.id;
      COMMIT;
      END LOOP;

      EXCEPTION WHEN OTHERS THEN ROLLBACK TO TrnSavePointNull;
      DBMS_OUTPUT.PUT_LINE(dbms_utility.FORMAT_ERROR_BACKTRACE());
      END;