DECLARE EXCEPTION

The DECLARE EXCEPTION statement in SQL Script declares an exception.

Syntax

DECLARE [PUBLIC] <exceptName>

EXCEPTION

Remarks

•

An exception can be declared in a child scope that has the same name as the one declared in the parent scope. In that case, the one in the parent scope is not visible within the child scope.

•

You can define exceptions by providing a unique name to each exception. See also External Exceptions, Attributes of CURRENT_EXCEPTION, and Raising and Handling Exceptions.

•

The PUBLIC keyword can only be used in the root compound statement of a PROCEDURE. It makes the exception visible outside the procedure as described in the section External Exceptions. See Compound Statements for information on compound statements.

Examples

PROCEDURE f(IN x INTEGER)

BEGIN

  DECLARE PUBLIC illegal_arg_ex EXCEPTION;

  IF x IS NULL THEN

    RAISE illegal_arg_ex;

  END IF;

...

END

PROCEDURE p(IN x INTEGER, IN result BIT)

BEGIN

  CALL /shared/f(x);

  SET result = 1; -- success

EXCEPTION 

  WHEN /shared/f.illegal_arg_ex THEN

    SET result = 0; --failure

END