ORA-01008 in MERGE statement in 10gR3

Hi

I am not sure why I am getting the following ORA error in MERGE statement and in dynamic sql. Both the tables are local.

If MERGE is replaced with individual insert/update statements it works fine.

desc test

 Name                                      Null?    Type


 ----------------------------------------- -------- -----------------


 COL1                                               VARCHAR2(10)
 COL2                                               NUMBER

desc test1

 Name                                      Null?    Type


 ----------------------------------------- -------- -----------------


 COL1                                               VARCHAR2(10)
 COL2                                               NUMBER

CREATE OR REPLACE
PROCEDURE TEST_PROC AS
v_proc varchar2(4000);
mycol number := 10 ;
BEGIN
  v_proc := ' merge into test a using test1 b on (a.col1=b.col1) when matched then ';

  v_proc := v_proc||' update set col2 = :col2 ';
  v_proc := v_proc||' when not matched then ';
  v_proc := v_proc||' insert (col1, col2) values ( b.col1, :col2) ';

   execute immediate v_proc using mycol ;   exception
  when OTHERS then
    dbms_output.put_line('Error updating '|| v_proc);     dbms_output.put_line('Message - '||substr(SQLERRM, 1, 200)); end;

END TEST_PROC; Message - ORA-01008: not all variables bound

Thanks

Sam Received on Fri May 30 2008 - 19:24:23 CDT