Limitations for routines written in NZPLSQL

For a routine written in NZPLSQL, the following limitations apply.

  • The routine must define exactly one alias for each of its parameters.
  • The routine cannot contain argument lists or variable arguments (varargs).
  • If the routine returns a non-null value that has a data type other than binary integer or Boolean, the routine will automatically be converted to a MODIFIES SQL DATA compiled SQL function. This function can then be invoked in the same way as any other compiled SQL function.
Also, use of the following syntax keywords is restricted:
Syntax Keyword Description
IN EXECUTE If the routine uses a FOR...IN EXECUTE statement to iterate through the results of a query, it must use a structured record (see Records in a FOR...IN EXECUTE statement).
INTERVAL The routine cannot use the INTERVAL data type.
LAST_OID The routine cannot use the LAST_OID clause.
RAISE The routine cannot use the DEBUG level for RAISE. It can use the NOTICE level, but SERVER OUTPUT must be set to ON for you to be able to see the output.
SELECT The routine can contain a SELECT statement only if that statement contains a FROM clause. It cannot use a SELECT statement to call another procedure or to retrieve a scalar value. To call another procedure, the routine must use a CALL statement.
TIMETZ The routine cannot use the TIMETZ data type.
TRANSACTION_ABORTED The routine cannot issue TRANSACTION_ABORTED exceptions. All exceptions it issues are routed to OTHERS.