How to stop execution when will get the ERROR in a perticular block?

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • kiranrajenimbalkar
    New Member
    • Jan 2010
    • 13
    #1

    How to stop execution when will get the ERROR in a perticular block?

    Hi,

    I have to execute below Anonymous Blocks script which inclide update script,but if suppose I will get error on block 5 then another block will not be execute i.e 6,7,8,9......la st block ,so please request to you that please provide me the solution ASAP how i will do this.

    Code:
    BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (3128, 6057, 6779, 7057, 7084, 7328);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 2 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 2');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (1102147, 1102367, 1102424, 1103296);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 3 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 3');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (1493634, 1493731, 1493925);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 4 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 4');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (1773803, 1773910, 1774804, 1774962, 1775215);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 5 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 5');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (2030398, 2030723, 2030756, 2030857);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 6 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 6');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (2238623, 2238629, 2238669, 2238957);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 7 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 7');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (2434597, 2434630, 2434797, 2435370);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 8 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 8');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (3107735, 3108103, 3108465, 3109176);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 9 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 9');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (3333340, 3333470, 3333722);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 10 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 10');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (3466145, 3466170, 3466257);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 11 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 11');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (5186888, 5186971, 5187025);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 12 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 12');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (5326971, 5327008, 5327036);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 13 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 13');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (5459438, 5459580);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 14 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 14');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (5608661, 5608838, 5608925);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 15 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 15');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (5719250, 5719492, 5719533);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 16 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 16');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (5837101, 5837142, 5837217);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 17 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 17');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (5944554, 5944623, 5944677);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 18 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 18');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6052153, 6052254, 6052355);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 19 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 19');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6154086, 6154189, 6154206);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 20 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 20');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6239782, 6239909);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 21 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 21');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6330597, 6330929, 6330986);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 22 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 22');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6407050, 6407239, 6407264);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 23 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 23');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6480726, 6480732, 6480733);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 24 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 24');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6542096, 6542114, 6542147);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 25 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 25');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6599366, 6599425, 6599437);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 26 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 26');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6657973, 6658073, 6658077);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 27 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 27');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6715103, 6715104, 6715131);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 28 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 28');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6763225, 6763343, 6763360);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 29 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 29');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6807787, 6807867, 6807988);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 30 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 30');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6852737, 6852747, 6852754);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 31 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 31');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6889328, 6889375, 6889381);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 32 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 32');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6927937, 6927983, 6928014);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 33 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 33');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (6962713, 6962729, 6962970);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 34 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 34');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 2
    WHERE registration_type IS NULL
      AND ID IN (7013628, 7013629, 7013672);

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 35 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 35');
         ROLLBACK;
         RAISE;
      END;
END;
/

BEGIN
   UPDATE webuser
      SET registration_type = 1
    WHERE registration_type IS NULL;

   COMMIT;
   DBMS_OUTPUT.put_line ('Block 36 executed successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line ('Error occurs at block 36');
         ROLLBACK;
         RAISE;
      END;
END;
/
    Tags: None
    • magicwand
      New Member
      • Mar 2010
      • 41
      #2
      I won't comment on your application - design, but the thing you are looking for is (in SQL*Plus):

      WHENEVER SQLERROR EXIT FAILURE

      and/or

      WHENEVER OSERROR EXIT FAILURE

      (SQL*Plus User’s Guide and Reference p.12-192 ff)

        Comment

        Previous template Next
        Working...