1. Function should accept only IN parameters (not OUT, IN OUT).
2. Function should accent and return only valid SQL data types (not PL/SQL specific types like boolean, record and etc.).
3. Parameters must be specified with positional notation (not named notation '=>').
4. User should have a Execute privilege.
The above restrictions are quite simple and I can't add something to it. I would like to create a couple of examples about 'DML' and 'Select' restrictions.
There is an easy test case:
SQL> create table test1 (id number, name varchar2(100));
Table created.
SQL> insert into test1 select level, 'name'||level from dual connect by level <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select * from test1;
ID NAME
---------- -----------------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
7 name7
8 name8
9 name9
10 name10
10 rows selected.
SQL> create table test2 (id number);
Table created.
SQL>
1. Functions called from select statement can't contain DML statements.
SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
2 return test1.name%type
3 is
4 v_name test1.name%type;
5 begin
6 insert into test1 (id, name) values (-1,'test'); -- DML
7 return 'test';
8 end func_sql_run_test;
9 /
Function created.
SQL> select func_sql_run_test(1) from test1 where id =1;
select func_sql_run_test(1) from test1 where id =1
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL>
2. Functions called from update, delete and insert .. select statement can't query or modify the same table.
2.1. Example 1.
SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
2 return test1.name%type
3 is
4 v_name test1.name%type;
5 begin
6 select name into v_name from test1 where id = p_id;
7 return v_name;
8 end func_sql_run_test;
9 /
Function created.
SQL> update test1 set name = func_sql_run_test(1) where id = 10;
update test1 set name = func_sql_run_test(1) where id = 10
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> delete from test1 where name = func_sql_run_test(1);
delete from test1 where name = func_sql_run_test(1)
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> insert into test1 (id, name) select 11, func_sql_run_test(1) from dual;
insert into test1 (id, name) select 11, func_sql_run_test(1) from dual
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> -- insert works fine
SQL> insert into test1 (id, name) values (11, func_sql_run_test(1));
1 row created.
SQL> rollback;
Rollback complete.
SQL>
2.2. Example 2.
SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
2 return test1.name%type
3 is
4 v_name test1.name%type;
5 begin
6 insert into test1 (id, name) values (-1,'test'); -- DML
7 return 'test';
8 end func_sql_run_test;
9 /
Function created.
SQL> update test1 set name = func_sql_run_test(1) where id = 10;
update test1 set name = func_sql_run_test(1) where id = 10
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> delete from test1 where name = func_sql_run_test(1);
delete from test1 where name = func_sql_run_test(1)
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> insert into test1 (id, name) select 11, func_sql_run_test(1) from dual;
insert into test1 (id, name) select 11, func_sql_run_test(1) from dual
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> -- insert works fine
SQL> insert into test1 (id, name) values (11, func_sql_run_test(1));
1 row created.
SQL> rollback;
Rollback complete.
SQL>
If I change the table name in DML statement, the function can be invoked through DML.
SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
2 return test1.name%type
3 is
4 v_name test1.name%type;
5 begin
6 insert into test2 (id) values (-1);
7 return 'test';
8 end func_sql_run_test;
9 /
Function created.
SQL> update test1 t set name = func_sql_run_test(1) where id = 10;
1 row updated.
SQL> select * from test2;
ID
----------
-1
SQL> delete from test1 where name = func_sql_run_test(1);
1 row deleted.
SQL> rollback;
Rollback complete.
SQL> insert into test1 (id, name) select 11, func_sql_run_test(1) from dual;
1 row created.
SQL> insert into test1 (id, name) values (11, func_sql_run_test(1));
1 row created.
SQL> rollback;
Rollback complete.
Additionally, there are some additional restrictions related with transactions (commit, rollback, DDL,DCL and etc.) Of course, all these restrictions are obvious, but I still decided to write about it because it's quite important and I can use this post as a hint :).
