To be callable from SQL statements a stored function (and any subprograms called by that function) must obey certain purity rules which are meant to control side effects:When called from a SELECT statement or a parallelized INSERT UPDATE or DELETE statement the function cannot modify any database tables.
When called from an INSERT UPDATE or DELETE statement the function cannot query or modify any database tables modified by that statement.
When called from a SELECT INSERT UPDATE or DELETE statement the function cannot execute SQL transaction control statements (such as COMMIT) session control statements (such as SET ROLE) or system control statements (such as ALTER SYSTEM). Also it cannot execute DDL statements (such as CREATE) because they are followed by an automatic commit.
If any SQL statement inside the function body violates a rule you get an error at run time (when the statement is parsed).
To check for violations of the rules you can use the pragma (compiler directive) RESTRICT_REFERENCES. The pragma asserts that a function does not read or write database tables or package variables. For example the following pragma asserts that packaged function credit_ok writes no database state (WNDS) and reads no package state (RNPS):
CREATE PACKAGE loans AS FUNCTION credit_ok RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES (credit_ok WNDS RNPS); END loans;
3 
0