Hey many updates is too many for a rollback?

Discussion in 'Databases' started by Jamie18, Aug 26, 2009.

  1. #1
    I wrote a script that seems like it enters an infinite loop or something.. I begin running it and it doesn't want to end..

    my question is by setting a savepoint at the beginning of the script am I blowing the stack? the script makes about 260 000 insertions, i set the savepoint before all insertions and a rollback on error.

    after running the script i checked out the tables and the script actually did what it was supposed to do other than terminate.. what's the deal?

    DECLARE
       today DATE := SYSDATE;
       creator VARCHAR2(80) := 'ETDS_MIG';
       next_actr_seq_no NUMBER(10,0);
       
       CURSOR all_contacts IS
          SELECT c.contact_id
          FROM   dbo_contacts c
          WHERE  c.contact_id NOT
                    IN (SELECT   acm.contact_id
                        FROM     tmp_actr_cntct_map acm);
                     
    BEGIN
       
       SAVEPOINT new_contact_map_entries;
       
       FOR i IN all_contacts LOOP
       
          SELECT   tmp_actr_seq_no.nextval
          INTO     next_actr_seq_no
          FROM     dual;
         
          INSERT
          INTO     tmp_actr_cntct_map
                   (actr_seq_no,
                    contact_id)
          VALUES   (next_actr_seq_no,
                    i.contact_id);
                    
          INSERT   -- Insert actr_seq_no into actor table
          INTO     etds_actr_tbl
                   (actr_seq_no,
                    actr_nte_txt,
                    actr_ste_seq_no,
                    xpry_dt,
                    actr_del_ind,
                    actr_del_dt,
                    rec_creatd_dt,
                    rec_creatd_usr_nm,
                    rec_ltst_chng_dt,
                    rec_ltst_chng_usr_nm)
          VALUES   (next_actr_seq_no,
                    NULL,
                    1,        -- Default as ACTIVE = actor state
                    NULL,     -- Default to no expiry date
                    0,        -- Default to not deleted
                    NULL,     -- Default to not deleted
                    today,    -- Default to sysdate = created date
                    creator,  -- Default to ETDS_MIG = creator
                    today,    -- Default to sysdate = latest change date
                    creator); -- Default to ETDS_MIG = latest change user
          COMMIT;
       END LOOP;
       
       
       DELETE
       FROM     tmp_actr_cntct_map acm
       WHERE    acm.contact_id NOT
                   IN (SELECT   c.contact_id
                       FROM     dbo_contacts c);
       COMMIT;
       
       DELETE
       FROM     etds_cntct_tbl c
       WHERE    c.actr_seq_no NOT
                   IN (SELECT   acm.actr_seq_no
                       FROM     tmp_actr_cntct_map acm);
       COMMIT;
       
    EXCEPTION
       WHEN others THEN
          RAISE_APPLICATION_ERROR(-20010, 'ERROR in update_tmp_actr_cntct_map.sql.');
          DBMS_OUTPUT.put_line('ERROR in update_tmp_actr_cntct_map.sql.');
          ROLLBACK TO new_contact_map_entries;
       
    END;
    Code (markup):
     
    Jamie18, Aug 26, 2009 IP