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