Functions and procedures in the WITH Clause

When appropriate, it is more convenient to immediately use the function inside the WITH clause instead of creating it in the schema

create table t_src (id number);
insert into t_src values (100); commit;
create table t_test (date_op date, x number);

WITH
 FUNCTION with_function(p_val IN NUMBER) RETURN NUMBER IS
  BEGIN
  execute immediate 'insert into t_test (date_op,x) values (sysdate,:numval)'     using in p_val;
  commit;
   RETURN p_val;
  END;
SELECT with_function(100) FROM t_src WHERE rownum = 1;
/

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at “WITH_FUNCTION”, line 3

DMLs update/insert/delete won’t work inside a function called from SELECT except for autonomous transactions:

WITH
 FUNCTION with_function(p_val IN NUMBER) RETURN NUMBER IS
  PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
     execute immediate 'insert into t_test (date_op,x) values (sysdate,:numval)' using in p_val;
    commit;
      RETURN p_val;
   END;
SELECT with_function(100) FROM t_src WHERE rownum = 1;
/

WITH_FUNCTION(100)

100

1 row selected.