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 Valid in SPL Statement Blocks.

If you call the SPL routine as part of a DML statement (namely, an INSERT, UPDATE, DELETE, MERGE, or SELECT statement), the routine cannot execute any of the following SQL statements:
  • 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
  • MERGE
  • RENAME COLUMN
  • RENAME DATABASE
  • RENAME SEQUENCE
  • RENAME TABLE
  • ROLLBACK WORK
  • SET CONSTRAINTS
  • TRUNCATE
  • UPDATE
HCL OneDB™ issues error -675 if an SPL routine whose calling context is a DML statement attempts to execute any of the SQL statements listed above.

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 Valid in SPL Statement Blocks.