Oracle SQL :Restrictions on Calling Functions from SQL

Restrictions on Calling Functions from SQL

Restrictions • When called from a SELECT statement or a parallelized UPDATE or DELETE statement, the function cannot modify any database tables.

• When called from an 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.

• The function cannot call another subprogram that breaks one of the above restrictions.

The code example in the slide shows an example of having a DML statement in a function.
The function DML_CALL_SQL contains a DML statement that inserts a new record into the EMP table.

This function is invoked in the UPDATE statement that modifies the salary of employee 7788 to the amount returned from the function.

The UPDATE statement returns an error saying that the table is mutating.

Consider the following example where the function QUERY_SQL queries the SALARY column of the EMPLOYEE table:

CREATE OR REPLACE FUNCTION query_sql (a NUMBER)
   RETURN NUMBER
IS
   s   NUMBER;
BEGIN
   SELECT   sal
     INTO   s
     FROM   emp
    WHERE   empno = 7788;

   RETURN (s + a);
END;

/
UPDATE   emp
   SET   salary = query_sql (100)
 WHERE   empno = 7788

Error :ORA-04091: table APPS.EMP is mutating, trigger/function may not see it
ORA-06512: at "APPS.QUERY_SQL", line 6