ORA-01008 in MERGE statement in 10gR3

[samdba]

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

[Ana C. Dent]

samdba <oracle...@yahoo.com> wrote in news:aa31bc32-0b9d-4184-8ce4-
8a4fde...@t12g2000prg.googlegroups.com:

>in 10gR3

When did Oracle release 10gR3?
What is its real version to 4 decimal places?

[Dan Blum]

samdba <oracle...@yahoo.com> wrote:
> 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 ;

You have two bind variables and are only providing one value. Oracle does
not care about the names of the bind variables and so does not know that you
want to use the same value for each.

--
_______________________________________________________________________
Dan Blum to...@panix.com
"I wouldn't have believed it myself if I hadn't just made it up."

[samdba]

On May 30, 8:53 pm, t...@panix.com (Dan Blum) wrote:

> samdba <oracleing...@yahoo.com> wrote:
> > 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 ;
>
> You have two bind variables and are only providing one value. Oracle does
> not care about the names of the bind variables and so does not know that you
> want to use the same value for each.
>
> --
> _______________________________________________________________________

> Dan Blum t...@panix.com

> "I wouldn't have believed it myself if I hadn't just made it up."

Oops. My bad. Thanks for your input. It works fine now.

Thanks

Sam