Restrictions on Calling Functions from SQL expressions

Recently, I've decided to start preparation for 1Z0-146 exam and the first exam topic is 'List restrictions on calling functions from SQL expressions'. Let's start from basic and obvious restrictions:.

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