Semantics of Oracle stored procedures / functions in a transactional context

Question

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?

score 6 · Accepted Answer · answered Jul 28 '11 at 10:22

You can use RESTRICT_REFERENCES to indicate that a function won't read/write package or database state.

CREATE PACKAGE t_pkg AS
   FUNCTION showup (msg VARCHAR2) RETURN VARCHAR2;
   PRAGMA RESTRICT_REFERENCES(showup, WNDS, RNDS);
END t_pkg;
/
-- create the package body
CREATE OR REPLACE PACKAGE BODY t_pkg AS
   FUNCTION showup (msg VARCHAR2) RETURN VARCHAR2 IS
    v_val varchar2(1);
   BEGIN
      select dummy into v_val from dual;
      RETURN v_val;
   END;
END t_pkg;
/

It 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.

I'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.

that's where I was aiming. Procedures - by design - can only be called from PL/SQL, so that's a good means of communicating the intended scope of the routine. I didn't know about that original restriction though. I guess it was too restrictive...? — Lukas Eder, Jul 28 '11 at 11:23

score 2 · Answer 2 · edited May 23 '17 at 11:43

2

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?

If 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).

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

So, having a function that requires a transaction itself should prevent it from being called from a SELECT.

edited May 23 '17 at 11:43

Community

1
1

answered Jul 28 '11 at 09:47

Nivas

18,126
4
62
76

"ORA-14551 cannot perform a DML operation inside a query" is a runtime error. So it always possible to compile such functions withour errors. As far as I could understand the problem is to prevent compiling such functions – Maxim Shevtsov Jul 28 '11 at 10:03
@Maxim: Almost. About compiling the SQL statement calling it in `select .. from dual` – Lukas Eder Jul 28 '11 at 10:10
@Maxim: The question was about preventing the call to such functions from a `SELECT` sql. `SELECT` sqls need not be always compiled (and mostly are not), and by nature they themselves are called "at runtime". `ORA-14551` is going to prevent you from doing so. – Nivas Jul 28 '11 at 10:20
@Nivas: Good point. So maybe that's a good reason to use stored procedures instead, to communicate the fact that a routine shouldn't be used in SQL, only in PL/SQL – Lukas Eder Jul 28 '11 at 11:19

score 1 · Answer 3 · answered Jul 28 '11 at 10:39

From my point of view there is no way to accomplish this.Although you can avoid runtime errors like "ORA-14551" by using PRAGMA RESTRICT_REFERENCES in "our_function(1, 2, 3)" to be sure that it is safe to use it the SQL query,but you can't prevent it from using in sql at the compiler-level

Semantics of Oracle stored procedures / functions in a transactional context

3 Answers3

Linked