Skip to content
Advertisement

ORACLE SQL How to use custom function iside insert cte

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

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement