I have recently had some input from a colleague regarding committing in a stored function. Whether we use procedures or functions to execute offline / batch logic in an Oracle database is mostly a matter of taste in our application. In both cases, we return a code either as function result, or as procedure OUT parameter. We usually require those offline / batch routines to be called from PL/SQL, not from SQL:
-- good
declare
rc number(7);
begin
rc := our_function(1, 2, 3);
end;
-- less good
select our_function(1, 2, 3) from dual;
The reason why the latter is less good is because our_function may commit the transaction for performance reasons. This is ok for a batch routine.
The question is: Are there any best practices around this topic, or some special keywords that prevent such functions from being used in SQL statements on a compiler-level? Or should we avoid functions for batch operations and only use procedures?
Gary Myers :
You can use RESTRICT_REFERENCES to indicate that a function won't read/write package or database state.\n\nCREATE PACKAGE t_pkg AS\n FUNCTION showup (msg VARCHAR2) RETURN VARCHAR2;\n PRAGMA RESTRICT_REFERENCES(showup, WNDS, RNDS);\nEND t_pkg;\n/\n-- create the package body\nCREATE OR REPLACE PACKAGE BODY t_pkg AS\n FUNCTION showup (msg VARCHAR2) RETURN VARCHAR2 IS\n v_val varchar2(1);\n BEGIN\n select dummy into v_val from dual;\n RETURN v_val;\n END;\nEND t_pkg;\n/\n\n\nIt used to be the case that SQL wouldn't allow you to call a function unless it made such a promise, but that restriction got dropped.\n\nI'd prefer to make it a differentiator between a procedure and a function. It's worth bearing in mind that if a PL/SQL function raises a NO_DATA_FOUND exception, a calling SQL statement does not fail (as no data found isn't an SQL error). So I prefer to use procedures unless the object is specifically designed to be called from SQL.",
2011-07-28T10:22:48
Nivas :
\n Are there any best practices around this topic, or some special\n keywords that prevent such functions from being used in SQL statements\n on a compiler-level?\n\n\nIf you use a function that requires a transaction (and therefore a commit), AFAIK you will not be able to call it from a SELECT, unless the function uses an AUTONOMOUS TRANSACTION (otherwise you get a ORA-14551 cannot perform a DML operation inside a query).\n\nSee also: ORA-14551: cannot perform a DML operation inside a query\n\nSo, having a function that requires a transaction itself should prevent it from being called from a SELECT.",
2011-07-28T09:47:21