Re: EXCEPTION_WHEN_OTHERS

["Alex R. Mosteo" <devnull@mailinator.com>]

Chris wrote:
> Hi,
> 
> Could someone please help me with regards to a problem I am having with
> some PL/SQL code.

Jason already replied, but to further clarify: what you have here is a
misleading indentation. It should be

if :contract.old_contract IS NOT NULL AND
   :contract.new_contract IS NOT NULL 
THEN
   BEGIN
      UPDATE contracts
         SET contract_no = :contract.new_contract
       WHERE contract_no = :contract.old_contract;
      message ('update successful');
   EXCEPTION WHEN OTHERS THEN
      message ('update has failed');
   END;
END IF;

Note that EXCEPTION is at the same level than BEGIN. As you can clearly see
now, any exception raised between BEGIN and EXCEPTION will cause the
exception block to be executed, whereas if no exception occurs, all the
code before EXCEPTION will be executed.

> 
> The code is:
> 
> if :contract.old_contract IS NOT NULL AND
> :contract.new_contract IS NOT NULL THEN
> BEGIN
> UPDATE contracts
> SET contract_no = :contract.new_contract
> WHERE contract_no = :contract.old_contract;
> EXCEPTION WHEN OTHERS THEN
> message ('update has failed');
> END;
> END IF;
> 
> As you can see I have a message saying 'update failed' when there is an
> exception i.e. when the save fails.  This works fine as does the rest
> of the code.
> 
> What I need however is a way of getting a message 'save was successful'
> when there are no exceptions.
> 
> Any help would be great.
> 
> Thanks
> Chris