User defined functions is SQL??

[Gus]

I've always thought that user defined functions could not upate the database unless they were autonomous at least thats what i read in my plsql book. Yet i find i can do the following, here i find my function 'change_emp' is able to update the emp_ids of the table 'EMP'. What have i misunderstood?
Cheers Gus

CREATE TABLE EMP(emp_id NUMBER(3), NAME VARCHAR2(30), job VARCHAR2(50));

CREATE OR REPLACE FUNCTION change_emp
RETURN VARCHAR2
IS
--PRAGMA autonomous_transaction;
BEGIN
UPDATE EMP
SET emp_id = emp_id + 5;
RETURN 'Latest entry';
END;

INSERT INTO EMP VALUES(1, 'John Mills', 'Writer');

INSERT INTO EMP VALUES(2, 'David Gray', 'Singer/song writer');


Now if i use my user-defined function it makes changes to the DB??

INSERT INTO EMP VALUES(3, 'Blue Nile', change_emp);

[tamilselvan]

After this
INSERT INTO EMP VALUES(3, 'Blue Nile', change_emp);

What happened?
Tamil

[gamyers]

What you are not allowed to do is call a user-defined function that updates the database (non-autonomous) as part of a select. IE a select is not allowed to update the database.
In the example,the function is able to be executed and the value returned, and then the insert done.
You probably wouldn't be able to do an INSERT ... SELECT change_emp
Probably not an UPDATE either (the update needs to select the rows to be updated)

[Gus]

What if you didn't use the pragma autonomous transaction ...without the pragma the table cannot be updated by a UDF in a query as expected but from the rules a table should not be updateable by a UDF from within a dml statment either but it can be as I've shown below even though the rule states...

"When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement."

...I did, how can this be!


DevSQL>CREATE TABLE testtab (col1 NUMBER(3), remark varchar2(30));

Table created.

DevSQL>
DevSQL>CREATE OR REPLACE FUNCTION addone
2 RETURN VARCHAR2
3 IS
4 BEGIN
5 UPDATE testtab
6 SET col1 = col1 + 3;
7 RETURN 'Inserted by a UDF';
8 END;
9 /

Function created.

DevSQL>
DevSQL>INSERT INTO testtab VALUES(1, 'one');

1 row created.

DevSQL>
DevSQL>INSERT INTO testtab VALUES(2, 'two');

1 row created.

DevSQL>
DevSQL>INSERT INTO testtab VALUES(3, 'three');

1 row created.

DevSQL>
DevSQL>SELECT * FROM testtab;

COL1 REMARK

---------- ------------------------------
1 one
2 two
3 three

DevSQL>
DevSQL>SELECT col1, remark, addone "Updated Value" FROM testtab;
SELECT col1, remark, addone "Updated Value" FROM testtab
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "HR.ADDONE", line 5


DevSQL>
DevSQL>SELECT * FROM testtab;

COL1 REMARK
---------- ------------------------------
1 one
2 two
3 three

DevSQL>
DevSQL>INSERT INTO testtab VALUES(4, addone);

1 row created.

DevSQL>
DevSQL>SELECT * FROM testtab;

COL1 REMARK
---------- ------------------------------
4 one
5 two
6 three
4 Inserted by a UDF

[tamilselvan]

I think after the function is created, you executed it once.
I am not getting the wrong answer in 9iR2.
See below:

SQL> select * from F1 ;

       COL REMARK
---------- ------------------------------
         1 one
         2 two
         3 three
         4 four

SQL> set autocommit on
SQL> select a.*, upd_col() as abc from F1 a ;
select a.*, upd_col() as abc from F1 a
            *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "TAMIL.UPD_COL", line 5


SQL> select * from F1 ;

       COL REMARK
---------- ------------------------------
         1 one
         2 two
         3 three
         4 four

Tamil

[gamyers]

Broadly, if a function updates the database and multiple rows are being acted upon, there is the potential that if the rows are picked up in A,B,C order they will return one result, but another if they are picked up in C,B,A order.
To ensure this doesn't happen, it errors out.

INSERT VALUES is a special case. It knows that the INSERT will only be for one row and that executing the function cannot affect the outcome of the INSERT.

Autonomous transactions can cause the same problems, which is why they should be treated with great caution.