Re: RAISED EXCEPTION not well caught

Peter Wu wrote:

> Hello group,
>
> I'm new to Oracle PL/SQL.
>
> I have written a test script as follows.
>
> BEGIN
> select min(contract_start_date)
> into l_min_contract_start_date
> from contracts
> where customer_last_name = 'Bush';
>
> if (l_min_contract_start_date is null) then
> raise NO_DATA_FOUND;
> end if;
>
> BEGIN
>
> /* do something */
>
> EXCEPTION WHEN NO_DATA_FOUND THEN
> BEGIN
> /*
> do something to handle the exception within
> the nearest BEGIN
> */
>
> -- Exception Handler1
>
> END;
> END;
>
>
>
> EXCEPTION WHEN NO_DATA_FOUND THEN
> BEGIN
> /*
> do something to handle the excpetion raised by
> contract_start_date SELECT */
>
> -- Exception Handler2
>
> END;
> END;
> END;
>
>
> The problem I see is that when the NO_DATA_FOUND exception is raised
> after the SELECT, the program goes to Exception Handler1 instead of
> Exception Handler2 as I want it to.
>
> What was I missing? Any insight will be appreciated!

What is missing is a variable declaration

 >     select min(contract_start_date)
 >     into l_min_contract_start_date

Where is l_min_contract_start_date defined?

DECLARE l_min_contract_start_date DATE;

BEGIN

• code here EXCEPTION
• exception handler here END; /

In the future post complete error messages and Oracle verison.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)