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?
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