ORA-00904: invalid identifier during merge PLSQL

Question

I am trying to run merge statement in following manner in PLSQL block and getting ORA-00904 error described below:

--Merge statement

 l_mergestr1:='merge into pi_customer picu using (select * from table(picu_var)) src ' ||
     ' on (picu.customer_id = src.customer_id) when matched then update set '||l_newmergeupstr1|| 
     ' where picu.time_stamp <> pctemp.time_stamp when not matched then' || 
     ' insert ('||l_newmergeinsstr1||') values ('||l_newmergeinstvalstr1||')'; 
      execute immediate l_mergestr1;

ORA-00904: "PICU_VAR": invalid identifier

Here picu_var is a variable of object_type which I have already declared. Using dbms_output I verified the merge statement converts to following which I think is a correct syntax for merge and it works :

merge into pi_customer picu using (select * from table(picu_var)) src  
      on (picu.customer_id = src.customer_id) when matched then update set picu.Customer_Name=src.Customer_Name,picu.Server_Name=src.Server_Name,picu.Time_stamp=src.Time_stamp
      where picu.time_stamp <> src.time_stamp when not matched then 
      insert (picu.Customer_ID,picu.Customer_Name,picu.Server_Name,picu.Time_stamp) values (src.Customer_ID,src.Customer_Name,src.Server_Name,src.Time_stamp)

I also verified there is no mismatch across source and target table column names and the merge syntax works when executed as what returned from dbms_output.
Please suggest how to avoid the error ORA-00904. Does the syntax for merge declared in "l_mergestr1" requires a change?

Tony Andrews Tony Andrews · Accepted Answer · 2015-08-04T10:39:12

You can't reference a variable from outside the dynamic SQL inside the dynamic SQL. Use a bind variable to pass it in:

l_mergestr1:='merge into pi_customer picu using (select * from table(:picu_bv)) src ' ||
     ' on (picu.customer_id = src.customer_id) when matched then update set '||l_newmergeupstr1|| 
     ' where picu.time_stamp <> pctemp.time_stamp when not matched then' || 
     ' insert ('||l_newmergeinsstr1||') values ('||l_newmergeinstvalstr1||')'; 
      execute immediate l_mergestr1 using picu_var;

ORA-00904: invalid identifier during merge PLSQL

1 Answers