Oracle observing all exceptions but not capturing

I have a stored procedure that I'm writing, and if it fails with any type of exception I want to respond in a specific way but I still want to pass the exception back to t开发者_Python百科he caller.

How can I do this?

DECLARE
   pe_ratio NUMBER(3,1);
BEGIN
   SELECT price / earnings INTO pe_ratio FROM stocks
      WHERE symbol = 'XYZ';  -- might cause division-by-zero error
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
   COMMIT;
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN  -- handles 'division by zero' error
      INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
      COMMIT;
   RAISE ; --This will reraise your exception
END;  -- exception handlers and block end here

If I understand your question correctly, you just need to re-raise the exception once you capture it. (see above in the exception block; based on example from here)

EDIT

From the link above: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm#1069

The optional OTHERS exception handler, which is always the last handler in a block or subprogram, acts as the handler for all exceptions not named specifically. Thus, a block or subprogram can have only one OTHERS handler.

EXCEPTION
   WHEN ZERO_DIVIDE  THEN
      -- handle the error
   WHEN NO_DATA_FOUND THEN
      -- handle the error
   WHEN OTHERS THEN
      -- handle all other errors
END;

There are caveats to using WHEN OTHERS, for instance you should avoid using the WHEN OTHERS to swallow errors and do nothing

...

WHEN OTHERS
NULL ; --swallowing the exception, not propagating it, just ignoring it.  This will be problematic!
END ;

however you could do this:

WHEN OTHERS
  LOGException(...) ; /** look at the asktom link for his example **/
  RAISE; --always follow up with a RAISE!
END ;

Have a look at asktom to see his views WHEN OTHERS!

But keep in mind, if you just want the exception to be propagated to the calling app, you don't need to capture it. Not capturing it, will automatically have it bubble up.