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.
In order to determine whether ...