Function called in SQL statement can't modify table data (WNDS - wriite no database state):
SQL> drop table tbl purge
2 /
Table dropped.
SQL> create table tbl(
2 str varchar2(50)
3 )
4 /
Table created.
SQL> create or replace
2 function f1(
3 p_str varchar2
4 )
5 return sys.OdciVarchar2List
6 is
7 begin
8 insert
9 into tbl
10 values(
11 'Insert within select is not allowed.'
12 );
13 return sys.OdciVarchar2List(p_str);
14 end;
15 /
Function created.
SQL> select *
2 from f1('ABC')
3 /
from f1('ABC')
*
ERROR at line 2:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.F1", line 7
SQL>
However, no error is raised for SQL macro:
SQL> create or replace
2 function f1(
3 p_str varchar2
4 )
5 return clob
6 sql_macro
7 is
8 begin
9 insert
10 into tbl
11 values(
12 'Insert within select is not allowed.'
13 );
14 return 'select * from sys.OdciVarchar2List(p_str)';
15 end;
16 /
Function created.
SQL> select *
2 from f1('ABC')
3 /
COLUMN_VALUE
---------------
ABC
SQL> select *
2 from f1('XYZ')
3 /
COLUMN_VALUE
---------------
XYZ
SQL> select *
2 from tbl
3 /
STR
--------------------------------------------------
Insert within select is not allowed.
Insert within select is not allowed.
SQL>
SY.