well, you can. (technically DML includes select, but you probably mean "dml that modifies things") even if the dml modifies stuff.
but - you probably don't want to.
why not? because modifications should be something deterministic, something you can count on, predicable.
and you have NO CONTROL WHATSOEVER AS TO HOW MANY TIMES WE CALL YOUR FUNCTION.
none, zippo, zero.
ops$tkyte%ORA10GR2> create table t ( id number, msg varchar2(4000) );
Table created.
ops$tkyte%ORA10GR2> create sequence s;
Sequence created.
ops$tkyte%ORA10GR2> create table data as select * from all_users;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function f return number
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into t values ( s.nextval, 'i was called ' );
6 commit;
7 return 42;
8 end;
9 /
Function created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from data where user_id = f;
no rows selected
ops$tkyte%ORA10GR2> select min(id), max(id), count(*) from t;
MIN(ID) MAX(ID) COUNT(*)
---------- ---------- ----------
1 39 39
so, apparently, we call it once per row (there are 39 rows in my data table), or do we...
ops$tkyte%ORA10GR2> create index t_idx on data(user_id);
Index created.
ops$tkyte%ORA10GR2> select * from data where user_id = f;
no rows selected
ops$tkyte%ORA10GR2> select min(id), max(id), count(*) from t;
MIN(ID) MAX(ID) COUNT(*)
---------- ---------- ----------
1 41 41
ok, so maybe we call it 2 times per query? sometimes? when we feel like it?
ops$tkyte%ORA10GR2> select * from data where user_id = (select f from dual);
no rows selected
ops$tkyte%ORA10GR2> select min(id), max(id), count(*) from t;
MIN(ID) MAX(ID) COUNT(*)
---------- ---------- ----------
1 42 42
Oh, I see, depending on how the query plan is done, it only calls it once
You have NO CONTROL over how many times we invoke your function, none.
and a simple plan change will change how many times we do.
an upgrade to the next release might change it.
gathering statistics, adding an index might change it.
So, while you can do it, it is not very useful or practical...