Thanks Connor, we always get right inputs from your Team. Can you please point us to the additional details of documentation w.r.to this, I tried searching in oracle docs but failed to find a clue that I have to use with WITH_PLSQL hint,

INSERT /*+ WITH_PLSQL */
      INTO  T1 (A1)
   WITH FUNCTION T11 (P_A1 VARCHAR2)
           RETURN NUMBER
        IS
        BEGIN
           IF P_A1 = 'A'
           THEN
              RETURN 10  ;

    ELSE
    RETURN 100;
    END IF;
    END;
SELECT T11 ('A1') FROM DUAL;

is working fine now, but when I try this as

begin 
INSERT /*+ WITH_PLSQL */
      INTO  T1 (A1)
   WITH FUNCTION T11 (P_A1 VARCHAR2)
           RETURN NUMBER
        IS
        BEGIN
           IF P_A1 = 'A'
           THEN
              RETURN 10  ;

    ELSE
    RETURN 100;
    END IF;
    END;
SELECT T11 ('A1') FROM DUAL;
commit;
end; 

am getting below error. Can't I use this in PL/SQL Block Connor?

ORA-06550: line 4, column 18:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 12, column 5:
PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:

( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge json_exists json_value json_query
json_object json_array
ORA-06550: line 15, column 5:
PLS-00103: Encountered the symbol "END"

I was getting an error with and without the the Oracle hint /*+ WITH_PLSQL */.

******
Found the solution as I was writing this and decided to post in case anyone else happens to run into this. When using the WITH function for an insert statement, the entire statement needs to end with /. Did not seem to need that when using only the WITH query. Only seemed to be needed when adding the WITH functions.
******

Database Access Tool: Oracle SQL Developer - version 17.2.0.188.1159
Database: Oracle Database 12.1.0.2.0

Simple create table.

create table aa_test (
    col1 varchar2(20)
    , col2 number
)
;

insert without the WITH function.

insert into aa_test (col1, col2)
with
    q as (
        select 'sqlTestVAL' as val1, 1 as val2
        from dual
    )
    
select val1, val2
from q
;

1 row inserted.
This works.

select * from aa_test
;

Now using the WITH functions

insert /*+ WITH_PLSQL */ into aa_test (col1, col2)
with
    function f_string return varchar2 is begin return 'functionTestVal'; end;
    function f_number return number is begin return 2; end;
    q as (
        select f_string as val1, f_number as val2
        from dual
    )
select val1, val2
from q
;
<b>/</b> --When using WITH functions for an Insert statement add <b>/</b> to avoid errors.

Gives error...
Error at Command Line : 12 Column : 1
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

Hello there,
I found problems when using using with function and /*+ WITH_PLSQL */ in stored procedure .
Please, follow the example, tested on 19c:
create table emp as select * from scott.emp where 1=0;
--intentionally deptno modified as varchar2
alter table emp modify deptno varchar2(10);
insert into emp select * from scott.emp;
update emp set deptno = 'a'||deptno
where rownum <=1;
--one row is not number as for deptno
create table emp1 as select * from emp where 1=0;
--now, we want to pass emp -> emp1

insert /*+ APPEND WITH_PLSQL */ into emp1(
EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
)
WITH
function to_num ( p_string IN varchar2) return number is
l_number number;
begin
l_number := p_string;
return l_number;
exception when others then
return null;
end;
SELECT EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,to_num(DEPTNO) DEPTNO
FROM emp
/
14 rows created
-- so far, so good


commit;
select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
...

-- note that empno 7369 has null for deptno, as expected, because depno in emp is not number
select *
from emp
where empno = 7369 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- -------
7369 SMITH CLERK 7902 17-DEC-80 800 a20


-- now, we want to create procedure with the insert statement above

valasekd@PDB1> create or replace procedure p_ins_emp as
2 begin
3 insert /*+ APPEND WITH_PLSQL */ into emp1(
4 EMPNO
5 ,ENAME
6 ,JOB
7 ,MGR
8 ,HIREDATE
9 ,SAL
10 ,COMM
11 ,DEPTNO
12 )
13 WITH
14 function to_num ( p_string IN varchar2) return number is
15 l_number number;
16 begin
17 l_number := p_string;
18 return l_number;
19 exception when others then
20 return null;
21 end;
22 SELECT EMPNO
23 ,ENAME
24 ,JOB
25 ,MGR
26 ,HIREDATE
27 ,SAL
28 ,COMM
29 ,to_num(DEPTNO) DEPTNO
30 FROM emp;
31 end;
32 /

Warning: Procedure created with compilation errors.

valasekd@PDB1> show err
Errors for PROCEDURE P_INS_EMP:

LINE/COL ERROR
-------- -------------------------------------------------------------
3/1 PL/SQL: SQL Statement ignored
14/10 PL/SQL: ORA-00905: missing keyword

Well, not possible. Probably bug.
Yes, it is possible to to perform insert statement in the procedure using execute immediate, but I really do not like this.

Dusan

Announced on:
https://community.oracle.com/tech/welcome/categories/15571

Thanks,
Dusan