Re: Doubt regards EXECPTION

Using an old debuggers trick, you can see that it does exactly what you expect it to do. Instead of just concatenating the watched variable, we used to put it between characters that (most likely) do not appear in the payload of the variable. That way you can, for instance, easily distinguish between a NULL string and a space. In this case, it will show what exactly was output by SQLERRM.

In this case, I used square brackets around SQLERRM in erro 2:

...

      exception
         when others then
           dbms_output.put_line ('erro 2: [' || sqlerrm || ']');
      end;

The output shows that the nested error is added to the original error message, rather than replacing it. The carriage return between the two messages causes the confusion:

SQL> /
erro 1: ORA-01476: divisor is equal to zero erro 2: [ORA-01476: divisor is equal to zero ORA-01476: divisor is equal to zero]
Apos erro2

PL/SQL procedure successfully completed.

Hope this helps,
Tony

On 23/09/12 05:54, Eriovaldo Andrietta wrote:
> Hello Friends,
> I have a doubt related to EXCEPTION.
>
>
> SQL>
> SQL> DECLARE
> 2 x number := 0;
> 3 y number := 100;
> 4 BEGIN
> 5 y := y / 0;
> 6 exception
> 7 when ZERO_DIVIDE then
> 8 dbms_output.put_line ('erro 1: ' || sqlerrm);
> 9 declare
> 10 begin
> 11 y := y / 0;
> 12 exception
> 13 when others then
> 14 dbms_output.put_line ('erro 2: ' || sqlerrm);
> 15 end;
> 16 dbms_output.put_line ('Apos erro2');
> 17 when others then
> 18 dbms_output.put_line ('erro others: ' || sqlerrm);
> 19 END;
> 20 /
>
> erro 1: ORA-01476: o divisor é igual a zero
> erro 2: ORA-01476: o divisor é igual a zero
> ORA-01476: o divisor é igual a zero
> Apos erro2
>
> *Understanding:*
>
> Exception 1
> y := y / 0; --> (the first one)
> when ZERO_DIVIDE then --> erro 1: ORA-01476: o divisor é igual a zero
>
> Exception 2
> y := y / 0; (the second one)
> when others then --> erro 2: ORA-01476: o divisor é igual a zero
> and show the message : ORA-01476: o divisor é igual a zero
>
>
> *My Doubt:*
>
> Why oracle shows the default exception : ORA-01476: o divisor é igual a
> zero
> There is a code that shows the erro 2 message
> and I understand that the default error should not appear ?
>
> Any idea ?
>
> Regards
> Eriovaldo
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l