Mastering Oracle SQL

Restrictions on Calling PL/SQL from SQL

While calling stored functions from SQL is a powerful feature, it is important to understand how doing so might have unintended consequences. For example, imagine that one of your co-workers has written a stored function that, given a part number, returns the number of times that part is included in all open orders. The function is contained in a utilities package such as the following:

CREATE OR REPLACE PACKAGE pkg_util AS
  FUNCTION get_part_order_qty(pno IN VARCHAR2) RETURN NUMBER;
END pkg_util;

You have been tasked with generating a weekly inventory report, and you would like to make use of the function in one of your queries, as in:

SELECT p.part_nbr, p.name, s.name, p.inventory_qty,  pkg_util.get_part_order_qty(p.part_nbr) open_order_qty
FROM part p, supplier s
WHERE p.supplier_id = s.supplier_id
ORDER BY s.name, p.part_nbr;

When you run the query, however, you are surprised to see the following error:

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

Upon checking the package body, you find that the get_part_order_qty function, along with calculating the number of times a part is included in all open orders, generates a request to restock the part by inserting a record into the part_order table if the calculated value exceeds the number in inventory. Had Oracle allowed your statement to be executed, your query would have resulted in changes to the database without your knowledge or consent.

Purity Level

In order to determine whether ...