Re: Doubt regards EXECPTION

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Tue, 25 Sep 2012 08:21:06 -0300
Message-ID: <CAJdDhaNd_GEXscNByRiKmiti=Symidi-LNYfAfRdMG5+TWDBdQ_at_mail.gmail.com>

Hi All,
Now I have an explanation about this.

Look at the code below, there are 3 exceptions that I put in the code, and when each one occurs, the SQLERRM keeps all inside it. It concatenates all excepetion. This is the reason why the message constains the default exception..

If we put 10 messages all of its will be printed.

This is my understanding.

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            declare
 16              begin
 17              y := y / 0;
 18              exception
 19                 when others then
 20                   dbms_output.put_line ('erro 3: ' || sqlerrm);
 21              end;
 22
 23              end;
 24              dbms_output.put_line ('Apos erro3');
 25    when others   then
 26      dbms_output.put_line ('erro others: ' || sqlerrm);

 27 END;
 28 /

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 --> default concatenead message erro 3: ORA-01476: o divisor é igual a zero ORA-01476: o divisor é igual a zero --> default concatenated message ORA-01476: o divisor é igual a zero --> default concatenated message Apos erro3

Best Regards
Eriovaldo

On Tue, Sep 25, 2012 at 7:13 AM, De DBA <dedba_at_tpg.com.au> wrote:

> Hi Eriovaldo,
>
> 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>
>>
>>
>>
>>
>>
>

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

Received on Tue Sep 25 2012 - 06:21:06 CDT