We can do either DDL or DML inside a function.
1) Let us try keeping a DML inside a function
1 create or replace function test (a number) return number as
2 begin
3 insert into holiday values(sysdate,sysdate);
4 return a;
5* end;
SQL> /
Function created.
method1:
SQL> select test(56) from dual;
select test(56) from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.TEST", line 3
Method2:
SQL> select * from holiday;
H_DT H_DESC
--------- ------------------
01-JAN-07 New Year
08-OCT-07 Columbus day
SQL> declare
2 abc number:=0;
3 begin
4 abc := test1(56);
5 dbms_output.put_line(abc);
6 end;
7 /
56
PL/SQL procedure successfully completed.
SQL> select * from holiday;
H_DT H_DESC
--------- ------------------
01-JAN-07 New Year
08-OCT-07 Columbus day
10-OCT-07 10-OCT-07
------------------------------------------------------------------------------------------
2) Let us alter the function to have a DDL instead of DML
1 create or replace function test (a number) return number as
2 begin
3 execute immediate 'drop table xyz';
4 return a;
5* end;
SQL> /
Function created.
Method1:
SQL> select test(56) from dual;
select test(56) from dual
*
ERROR at line 1:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "SCOTT.TEST", line 3
Method2:
SQL> create table xyz(a number);
Table created.
SQL> declare
2 abc number;
3 begin
4 abc:=test(56);
5 dbms_output.put_line(abc);
6 end;
7 /
56
PL/SQL procedure successfully completed.
SQL> desc xyz
ERROR:
ORA-04043: object xyz does not exist
