SQL> create table test_table(a int ,v varchar2(20),d date,c varchar2(20)) ;
Table created.
SQL> create or replace function test_function return varchar2
2 is
3 v_col varchar2(120);
4 CURSOR cur is
5 select count(*) from sys.test_table;
6 BEGIN
7 open cur;
8 fetch cur into v_col;
9 if cur%notfound then
10 insert into test_table values(4,'a',sysdate,'b');
11 commit;
12 else
13 delete from test_table ;
14 commit;
15 end if;
16 close cur;
17 return v_col;
18 END;
19 /
Function created.
SQL> select test_function from dual ;
select test_function from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYS.TEST_FUNCTION", line 13
SQL> create or replace function test_function return varchar2
2 is
3 PRAGMA AUTONOMOUS_TRANSACTION;
4 v_col varchar2(120);
5 CURSOR cur is
6 select count(*) from sys.test_table;
7 BEGIN
8 open cur;
9 fetch cur into v_col;
10 if cur%notfound then
11 insert into test_table values(4,'a',sysdate,'b');
12 commit;
13 else
14 delete from test_table ;
15 commit;
16 end if;
17 close cur;
18 return v_col;
19 END;
20 /
Function created.
SQL> select test_function from dual ;
TEST_FUNCTION
--------------------------------------------------------------------------------
0