EXCEPTION_WHEN_OTHERS

EXCEPTION_WHEN_OTHERS

[Chris]

Hi,

Could someone please help me with regards to a problem I am having with
some PL/SQL code.

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

Re: EXCEPTION_WHEN_OTHERS

[Jason King]

Actually this is an Ada group not pl/sql.  But since I'm a pl/sql guy I 
can help.
If you just put a line like
message('success');
after the update and before the exception you should be fine.
Chris wrote:
> Hi,
> 
> Could someone please help me with regards to a problem I am having with
> some PL/SQL code.
> 
> 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
> 

Re: EXCEPTION_WHEN_OTHERS

[Alex R. Mosteo]

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