Restrictions on SPL Routines in Data-Manipulation Statements

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

Restrictions on SPL Routines in Data-Manipulation Statements

If you call the SPL routine in a SQL statement that is not a data-manipulation language (DML) statement (namely EXECUTE FUNCTION or EXECUTE PROCEDURE), the SPL routine can execute any statement that is not listed in the section SQL Statements Not Valid in an SPL Statement Block.

In Dynamic Server, if you call the SPL routine as part of a DML statement (namely, an INSERT, UPDATE, DELETE, or SELECT statement), then the routine cannot execute any SQL statement in the following list:

ALTER ACCESS_METHOD
ALTER FRAGMENT
ALTER INDEX
ALTER OPTICAL CLUSTER
ALTER SEQUENCE
ALTER TABLE
BEGIN WORK
COMMIT WORK
CREATE ACCESS_METHOD

CREATE AGGREGATE
CREATE DISTINCT TYPE
CREATE OPAQUE TYPE
CREATE OPCLASS
CREATE ROLE
CREATE ROW TYPE
CREATE SEQUENCE
CREATE TRIGGER
DELETE

DROP ACCESS_METHOD
DROP AGGREGATE
DROP INDEX
DROP OPCLASS
DROP OPTICAL CLUSTER
DROP ROLE
DROP ROW TYPE
DROP SEQUENCE
DROP SYNONYM
DROP TABLE

DROP TRIGGER
DROP TYPE
DROP VIEW
INSERT
RENAME COLUMN
RENAME DATABASE
RENAME SEQUENCE
RENAME TABLE
ROLLBACK WORK
SET CONSTRAINTS
TRUNCATE
UPDATE

In Extended Parallel Server, if you call the SPL routine as part of a DML statement (namely, an INSERT, UPDATE, DELETE, MERGE, or SELECT statement), then the routine can execute only the following statements of SQL:

SELECT
SET PLOAD FILE
SET DEBUG FILE TO

SET EXPLAIN
SET OPTIMIZATION

For both Dynamic Server and Extended Parallel Server, these restrictions do not apply to an SPL routine that is invoked by a trigger, because in this case the SPL routine is not called by the DML statement, and therefore can include any SQL statement, such as UPDATE, INSERT and DELETE, that is not listed among the SQL Statements Not Valid in an SPL Statement Block.