Handling Exceptions from EXECUTE IMMEDIATE Statements

If the parser detects a syntax error when the EXECUTE IMMEDIATE statement is compiled, it issues a compilation error, and no executable UDR is produced until the syntax is corrected and recompiled. If the parser accepts the EXECUTE IMMEDIATE syntax and the UDR compiles successfully, but an exception occurs during a call to the UDR when the EXECUTE IMMEDIATE statement is executing, the database server issues an error at runtime. Runtime errors can be trapped by the WHENEVER statement, or by some other exception-handling mechanism in the program logic of the UDR.

For routines written in the SPL language, SQL expressions are evaluated at runtime, not when the routine is compiled or optimized. If an expression that follows the IMMEDIATE keyword specifies invalid SQL statement text, HCL OneDB™ issues a runtime exception, rather than a compilation error. After any runtime error condition in an SPL routine, program control passes to the ON EXCEPTION statement block (if this is defined); otherwise, execution of the UDR terminates abnormally, and an error is returned to the calling context. For information on how to handle runtime errors in SPL routines, see the descriptions of the SPL statement ON EXCEPTION. (See also the built-in SQL function SQLCODE.)