2

One of the main differences between UDF and SP is that UDF can only have select statements inside it and not insert/update/delete statements. Can someone please explain the reason behind this? The below function:

create function test(..)
...
BEGIN 
insert into EMPLOYEE('22',12000,'john');
return 0;
END

is not valid. But why is this so?

3

1 Answer 1

2

This is not about function (UDF) vs procedure. It is the context where you use them.

You can have DML operations in a function:

SQL> create table t1 (c1 number);

Table created.

SQL> create or replace function f1 return number as
  2  begin
  3    insert into t1 values (1);
  4    commit;
  5    return 0;
  6  end;
  7  /

Function created.

SQL> declare
  2    i number;
  3  begin
  4    i := f1;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from t1;

        C1
----------
         1

But if you use your function as and UDF in a query:

SQL> select f1 from dual;
select f1 from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "BP.F1", line 3

You can still work around this with an autonomous transaction:

SQL> create or replace function f2 return number as
  2    pragma autonomous_transaction;
  3  begin
  4    insert into t1 values (1);
  5    commit;
  6    return 0;
  7  end;
  8  /

Function created.

SQL> select f2 from dual;

        F2
----------
         0

SQL> select * from t1;

        C1
----------
         1
         1

But should you do this? I do not think so.

3
  • 2
    Your last statement still begs another "Why?". And I think the answer to that "why" would work as the answer to the OP's "why" too, even though the OP was incorrect about permissibility of DML in functions. Just saying. I like the answer anyway, learned something new.
    – Andriy M
    Apr 27, 2018 at 14:26
  • @AndriyM I find it a very bad idea to create code with unexpected side effects. If I run a query to just read data, I expect it to query data, and not modify something else in the background. Yes, I know, auditing, block cleanout and other mechanisnms can cause unexcpected writes on reads as well... Apr 27, 2018 at 15:13
  • Oh, you don't need to tell me, because my position on this is exactly the same as yours. It's shaping it into words that I have trouble with, otherwise I would've posted my own answer or edited yours :)
    – Andriy M
    Apr 27, 2018 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.