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

ORA-01795 - maximum number of expressions in a list is 1000 error

Recently, while loading a lot of data into DWH, I've faced with the ORA-01795 error. Some of my colleagues wrote the following code:
declare 
  v_user_name clob;
begin
    ...
    select wm_concat('''' ||username) || ''''),
      into v_user_name
      from customers;
    ...
    execute immediate
            'insert into temp_customer (user_name)
             select username
               from  tab_1
              where username in (' || v_user_name || ')';
    ...
end;
As you can see, v_user_name variable is populated by the first select statement. It contains number of customers divided by comma (why has my colleague decided to use undocumented "wm_concat" function - its another question, I hope there was a reason for that :) ) and after that, we use v_user_name variable into dynamic sql. This code had been working fine for years, but recently we had to load a lot of data (a lot of customers) into DWH and we've faced with the ORA-01795. I found a topic in OTN forum about this issue.

First of all, I decided to use select * from table (sys.odcivarchar2list ... ) construction (I am a very lazy person :), and didn't want to rewrite this code), but when I tried to use the above construction I received another error - ORA-00939: too many arguments for function. This approach didn't work. After that, I decided to divide the code into small parts - I had to rewrite the package a bit and it works fine. This solution works fine.
Of course, there is one more approach - create a temp table and just join it to the query. I think it is the best option.