Home / Questions / 299130
Accepted
Juan Pinzón
Asked: 2020-10-08 14:31:28 +0800 CST 2020-10-08 14:31:28 +0800 CST

How to catch exceptions in Oracle

  • 772

I am reviewing the exceptions in oracle and I have a problem, for example I am doing an sp that has a division by zero

procedure TEST(afectadas OUT NUMBER) 
IS
zero_div_e EXCEPTION 
divisor NUMBER;
division NUMBER;
BEGIN
  divisor := 0;
  IF (divisor = 0) THEN
    RAISE zero_div_e;
  ELSE 
    division := 10/DIVISOR;

  EXCEPTION
  WHEN zero_div_e THEN
    inserto into tabla1 (division) values (null);
  WHEN OTHERS THEN
    ROLLBACK;

END TEST;

The SP catches the exception of division by zero, or in this case a custom exception zero_div_e , however when inserting the data in table 1 in the division column, it is throwing another error which is that null cannot be inserted.

How can I catch this second exception?, since it does not return to the exception block

sql

1 Answers

  1. Best Answer

    You can use Begin/Exception/end block nesting to explicitly catch exceptions at specific points in your code as in:

    procedure TEST(afectadas OUT NUMBER) 
IS
  zero_div_e EXCEPTION 
  divisor NUMBER;
  division NUMBER;
BEGIN
  divisor := 0;
  IF (divisor = 0) THEN
    RAISE zero_div_e;
  ELSE 
    division := 10/DIVISOR;
  End if;
EXCEPTION
  WHEN zero_div_e THEN
      Begin
        insert into tabla1 (division) values (null);
      Exception
        when others then
             Dbms_Output.Put_Line('No se permite nulo');
      End;
WHEN OTHERS THEN
     ROLLBACK;
END TEST;
    • 1