Restrictions When Calling User Defined Functions From SQL Statements in Oracle PL/SQL

One of the powerful features of PL/SQL is Stored Function. A Function is a named PL/SQL block that can accept parameters and return a value. It is similar to Procedure which is also accept parameters and return one or more values through OUT parameters but one of the advantages of function over procedure is that we can call it as a part of expression from the SELECT statement. However, the following restrictions apply when calling functions from SQL statements.

1. Function called from a SELECT statement cannot contain DML statements.

Let's see what happen when I call a function containing DML statements from a SELECT statement.

I considered employees table of the HR schema and created the following function which contains an UPDATE statement that updates the salary of an employee and returns the updated salary.

CREATE OR REPLACE 
FUNCTION update_sal(P_emp_id IN NUMBER, 
                    P_sal    IN NUMBER) RETURN NUMBER IS
     L_new_sal NUMBER; 
BEGIN
     UPDATE employees
     SET salary  = salary + P_sal
     WHERE employee_id = P_emp_id
     RETURNING salary INTO L_new_sal;

     RETURN L_new_sal;
END;

Calling this function from a PL/SQL block will return the updated salary of an employee

BEGIN
  dbms_output.put_line(update_sal(100,1000));
END;

26000

However, when I execute this function from a SELECT statement I encountered with ORA-14551: cannot perform a DML operation inside a query error.

SELECT update_sal(100,1000) FROM dual;

ORA-14551: cannot perform a DML operation inside a query

2. Function called from an UPDATE or DELETE statement on a table T cannot query  or contain DML on the same table T
Let's create a function that contains a SELECT statement on employees table. The following function takes the employee_id as an input parameter and returns the first name of an employee.

CREATE OR REPLACE
FUNCTION get_emp_name(P_emp_id IN NUMBER) RETURN VARCHAR2 IS
   L_name VARCHAR2(30);
BEGIN
  SELECT first_name
  INTO L_name
  FROM employees
  WHERE employee_id = P_emp_id;

  RETURN L_name;
END;

When calling this function from an UPDATE statement on employees table, the UPDATE statement fails with ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it error.

UPDATE employees
SET first_name = get_emp_name(100)
WHERE employee_id =101;

ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it

Now let's execute a function that contains DML statement on table T from the UPDATE statement on same table T. Here, I am considering the same update_sal function which I created earlier. This function contains an UPDATE statement on employees table. Executing this function from an UPDATE statement on employees table fails the UPDATE statement with ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it error.

UPDATE employees
SET salary = update_sal(100,1000)
WHERE employee_id =101;

ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it

Remember, this restriction only applies if you call this function from UPDATE or DELETE statement. There is no restriction if you call it from INSERT statement. Following INSERT statement invokes two functions get_emp_name and update_sal and inserts a new employee record in employees table.

INSERT INTO employees VALUES(206,get_emp_name(100),
                             'King','SKING','123.567.8564',
                             sysdate,'AD_PRES',
                             update_sal(100,1000),NULL,100,90);

3. Function called from a SELECT, INSERT, UPDATE, or DELETE statement cannot execute Transaction Control Statement such as 

•   A COMMIT  or ROLLBACK statement
•   A session control statement (such as SET ROLE)
•   A system control statement (such as ALTER SYSTEM)
•   Any DDL statement

The following function takes employee_id as an input parameter, updates the salary on an employee and commits the transaction. 

CREATE OR REPLACE
FUNCTION increment_salary(P_emp_id IN NUMBER) RETURN NUMBER IS
   L_sal NUMBER;
BEGIN
  UPDATE employees
  SET salary = salary + 1000
  WHERE employee_id = P_emp_id
  RETURNING salary into L_sal;
  COMMIT;
  RETURN L_sal;
END;

When calling this function from an insert statement, it gives ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML error

INSERT INTO employees VALUES(206,get_emp_name(100),'King','SKING',
                             '123.567.8564',sysdate,'AD_PRES',
                             increment_salary(100),NULL,100,90);

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

4. Calls to subprograms that break these restrictions are also not allowed in the function.
Following procedure contains an UPDATE statement that updates the salary of an employee and returns the updated salary through OUT parameter

CREATE OR REPLACE PROCEDURE 
update_emp_salary(P_emp_id  IN NUMBER,
                  P_sal     IN NUMBER,
                  P_new_sal OUT NUMBER) IS
BEGIN
   UPDATE employees
   SET salary = salary + P_sal
   WHERE employee_id = P_emp_id
   RETURNING salary INTO P_new_sal; 
END;

Then, I created a function that invokes this procedure and returns updated salary on an employee.

CREATE OR REPLACE 
FUNCTION fun_update_sal(P_emp_id IN NUMBER, 
                        P_sal    IN NUMBER) RETURN NUMBER IS
   L_new_sal NUMBER;
BEGIN
   update_emp_salary (P_emp_id, P_sal, L_new_sal); 
   RETURN L_new_sal;
END;

Calling this function from a SELECT statement fails the statement and gives ORA-14551: cannot perform a DML operation inside a query error. Here function invokes the procedure which contains DML statement. Calling this function from a SELECT statements breaks the restriction and gives an error.

SELECT fun_update_sal(101,5000) FROM DUAL;

ORA-14551: cannot perform a DML operation inside a query

Other restrictions on the user-defined function include the following

•  The function must be stored in the database
•  The function must accept only IN parameters with valid SQL data types and not PL/SQL specific data types.
•  The function must return valid SQL data type and not PL/SQL specific data types.
•  The function cannot be called from the CHECK constraint clause of a CREATE TABLE of ALTER TABLE statement.
•  The function cannot be used to specify a default value for a column.