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) ';
begin
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
.