I found problems when using WITH function and /*+ WITH_PLSQL */ for SQL statement 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 emp1
where empno = 7369 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- -------
7369 SMITH CLERK 7902 17-DEC-80 800
-- 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 perform the insert statement in the procedure using execute immediate, but I really do not like this.
Dusan