SET Keyword in the ON EXCEPTION Statement

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Other Syntax Segments > Identifier >

SET Keyword in the ON EXCEPTION Statement

If you use a statement that begins with the keyword SET in ON EXCEPTION, you must enclose it in a BEGIN ... END statement block. The following list shows some of the SQL statements that begin with the keyword SET:

SET ALL_MUTABLES
SET AUTOFREE
SET CONNECTION
SET CONSTRAINTS
SET DATASKIP
SET DEBUG FILE
SET Default Table Space
SET Default Table Type
SET DEFERRED_PREPARE
SET DESCRIPTOR
SET ENCRYPTION
SEY ENVIRONMENT
SET EXPLAIN
SET INDEX

SET INDEXES
SET ISOLATION
SET LOCK MODE
SET LOG
SET OPTIMIZATION
SET PDQPRIORITY
SET PLOAD FILE
SET ROLE
SET SCHEDULE LEVEL
SET SESSION AUTHORIZATION
SET STATEMENT CACHE
SET TABLE
SET TRANSACTION
SET TRIGGER

The following examples show the incorrect and correct use of a SET LOCK MODE statement inside an ON EXCEPTION statement.

The following ON EXCEPTION statement returns an error because the SET LOCK MODE statement is not enclosed in a BEGIN ... END statement block:

ON EXCEPTION IN (-107)
   SET LOCK MODE TO WAIT; -- error, value expected, not 'lock'
END EXCEPTION;

The following ON EXCEPTION statement executes successfully because the SET LOCK MODE statement is enclosed in a BEGIN ... END statement block:

ON EXCEPTION IN (-107)
   BEGIN
   SET LOCK MODE TO WAIT; -- ok
   END
END EXCEPTION;