HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Oracle MERGE to remote DB - ORA-01008
ORACLE STUFFS
24-Sep-2009
Oracle Merge statement has some additional requirements when we process it thru DB-link. The first one is Oracle error "ORA-01008: not all variables bound" appearing despite it seems no variables are bound in the statement.

It appears when you use oracle function or variables in data modification clauses. For example:

....
When MATCHED then UPDATE set
  TRG.ACC_BAL_AMT=SRC.ACC_BAL_AMT
 ,TRG.UPD_DT=sysdate

The =sysdate used directly in the update clause causes the ORA-01008 issue.
You have to push the evaluation to the USING clause following way:

....
Using(
 select
    sysdate as SD
....
When MATCHED then UPDATE set
  TRG.ACC_BAL_AMT=SRC.ACC_BAL_AMT
 ,TRG.UPD_DT=SRC.SD

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky