Case wise Exception Handling in Oracle

3604 views oracle
3

How can I achieve below scenario using Oracle PL/SQL?

MY_STORED_PROC

BEGIN

UPDATE1;
IF UPDATE1 fails RAISE EXCP1
ELSE COMMIT;

UPDATE2;
IF UPDATE2 fails RAISE EXCP2
ELSE COMMIT;

EXCEPTION
WHEN EXCP1
INSERT LOG ('UPDATE1 Failed')
WHEN EXCP2
INSERT LOG ('UPDATE2 Failed')

END;

answered question

1 Answer

1

That is quite simple:

BEGIN
  BEGIN
    UPDATE1;
    COMMIT;
  EXCEPTION 
    WHEN OTHERS THEN
      RAISE EXCP1;
  END;

  BEGIN
    UPDATE2;
    COMMIT;
  EXCEPTION 
    WHEN OTHERS THEN
      RAISE EXCP2;
  END;
EXCEPTION
  WHEN EXCP1 THEN
    INSERT LOG ('UPDATE1 Failed');
  WHEN EXCP2 THEN
    INSERT LOG ('UPDATE2 Failed');
END;

You can use PRAGMA EXCEPTION_INIT to define your own exceptions to be raised.

posted this

Have an answer?

JD

Please login first before posting an answer.

Ads

Categories