I am trying to use function inside insert SQL, but I am getting error ORA-32034. Looks like that I couldn’t access this function in the cte. Maybe someone can help?
x
insert into table1 (
Field1,
Field2,
Field3
)
with
function STR_country(in_str VARCHAR2) return char is out_str char(2);
begin
out_str :='';
if SUBSTR(in_str,1,1)= 'A' then out_str :='AT';
elsif SUBSTR(in_str,1,1)= 'D' then out_str :='DE';
elsif SUBSTR(in_str,1,2)= 'CH' then out_str :='CH';
elsif SUBSTR(in_str,1,2)= 'CZ' then out_str :='CZ';
elsif SUBSTR(in_str,1,1)= 'H' then out_str :='HU';
else out_str := ' ';
end if;
return out_str ;
end;
help_select_transactions1 as
(select distinct
master.reference,
f.start_date,
f.end_date,
f.Branch_NO,
f.seq,
master.SOURCEAPPLICATIONCODE,
f.PAYRECIND,
f.NOSTRODDAINDICATOR,
f.PAYMENTVALUEDATE,
f.PAYMENTCURRENCY,
f.PAYMENTAMOUNT,
STR_country(customer1.address3) as customer_country,
STR_country(customer2.address3) as customer_country2)
Advertisement
Answer
If the query containing the PL/SQL declaration section is not the top level query, the top-level query must include the WITH_PLSQL hint. Without this hint, the statement will fail to compile
This says documentation. So in this case you will probably need:
insert /*+ WITH_PLSQL*/ into table1 (Field1, Field2,
dbfiddle with similiar table