Exceptions
You can define exceptions in SQL Script by providing a unique name for the exception and defining a procedure of that name to handle the exception condition.
Syntax
DECLARE [PUBLIC] <exceptionName> EXCEPTION
You can declare an exception in a child scope that has the same name as the one declared in the parent scope. If you do that, the one in the parent scope is not visible within the child scope.
Raising and Handling Exceptions
A BEGIN/END block in SQL Script can have an optional exception section.
Syntax
BEGIN
... ... ...
EXCEPTION
[WHEN <exceptionName>
[OR <exceptionName> …]
THEN <statements> …]
[ELSE <statements>]
END
Remarks
• If the EXCEPTION block is declared, it must contain at least one WHEN or one ELSE clause. An EXCEPTION block can contain any number of WHEN clauses, but only one ELSE clause.
• When an exception is raised in a BEGIN/END block, the first exception-handler WHEN clause that matches the exception is executed.
• All variables from the scope are available within the exception handler. This technique is different from Java, for example. In Java, nothing from the TRY block is available in the CATCH block. In SQL Script, all variables available within the BEGIN area are available within the EXCEPTION area. They do not go out of scope until END is reached.
• If an exception is not handled within a block, that block leaves scope as with a LEAVE statement and the same exception is raised in the parent scope, where it can be handled. If there are no further scopes, the exception is thrown out of the procedure to the caller. If the caller is SQL Script, SQL Script receives this error. If the caller is JDBC or a Java Procedure, a Java exception is received.
If the caller is in a SQL FROM clause, the statements ends with a runtime exception.
• Any exception raised while in an exception handler, immediately leaves the current scope as if it were an unhandled exception in this scope.
• Use the RAISE statement to raise an exception again.
Example
PROCEDURE p (IN x INTEGER, OUT result BIT)
BEGIN
DECLARE illegal_arg_ex EXCEPTION;
...
IF x < 0 THEN
RAISE illegal_arg_ex;
END
SET result = 1; --success
EXCEPTION
WHEN illegal_arg_ex THEN
SET result = 0; --failure
END
External Exceptions
System exceptions in SQL Script are considered to be globally reserved names, but they can be referenced by SQL Script procedures. If a user-defined exception is made public, it can be used by other procedures.
Syntax
<compNamespacePath>.<exceptionName>
Remarks
• You can invoke a system exception or other public exceptions from a SQL Script procedure by including a TDV namespace path (<compNamespacePath>) followed by a dot and the exception name (<exceptionName>) in the script.
• You can view the system exceptions available to SQL Script procedures on the Exceptions tab of /lib/util/System in Studio.
Example
/lib/util/System.NotFoundException