Re: strange behaviour of merge statement using native dynamic sql

From: Ron <support_at_dbainfopower.com>
Date: Wed, 11 Feb 2004 02:57:19 -0800
Message-ID: <RsGdnWjJAJWElLfdRVn_iw@comcast.com>

Hello Cris,

In regards to the Ora-1008 error, you are hitting the bug : 2141115

Abstract: ORA-1008 OR OERI:15212 FROM MERGE STATEMENT USING BIND VARIABLES

Details:
A MERGE statement with an inline view in the USING clause containing bind variables may incorrectly fail with ORA-1008 or ORA-600 [15212] errors.

Fixed in : 9.2.0.2, 9.2.0.1 (patch available for only some OS platforms)

Potential Workaround

---

Ensure all bind variables are eliminated from the ON() clause. This can be achieved by making any ON() clause binds into select list items in the USING() clause.

eg: MERGE INTO TTO T
USING ( SELECT :b1 BIND, b,c FROM Tfrom WHERE b=:b2 ) F ON ( t.a=F.BIND and t.b=f.b )
WHEN NOT MATCHED THEN
INSERT ( t.a , t.b, t.c ) VALUES ( 1,f.b, f.c ) WHEN MATCHED THEN
UPDATE SET t.c=f.c

Have a look at the above bug on Metalink for more details.

Regards,

  Ron
  DBA Infopower
  http://www.dbainfopower.com
  Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html

"Cris Carampa" <cris119_at_operamail.com> wrote in message news:c0curk$qol$1_at_panco.nettuno.it...
> Is someone able to explain to me the following behaviour (Oracle 9.0.1.3
> on SuSE Linux):
>
> SQL> create table x (
> 2 pk integer primary key,
> 3 data varchar2(4000)
> 4 ) ;
>
> Table created.
>
> SQL> create table y (
> 2 pk integer primary key,
> 3 data varchar2(4000)
> 4 ) ;
>
> Table created.
>
> SQL> insert into x values(1,'aaa') ;
>
> 1 row created.
>
> SQL> insert into x values(2,'bbb') ;
>
> 1 row created.
>
> SQL> insert into x values(3,'ccc') ;
>
> 1 row created.
>
> *************************************************************
> *** now I do create the following file and call it p.sql: ***
> *************************************************************
> declare
> dsql varchar2(32767) ;
> begin
> dsql :=
> ' merge into ' ||
> ' y tbl ' ||
> ' using ( ' ||
> ' select ' ||
> ' x.pk, ' ||
> ' x.data ' ||
> ' from ' ||
> ' x ' ||
> ' where ' ||
> ' x.pk = :1 ' ||
> ' ) qry ' ||
> ' on ( ' ||
> ' tbl.pk = qry.pk ' ||
> ' ) ' ||
> ' when matched then ' ||
> ' update ' ||
> ' set ' ||
> ' tbl.data = qry.data ' ||
> ' when not matched then ' ||
> ' insert ( ' ||
> ' tbl.pk, ' ||
> ' tbl.data ' ||
> ' ) values ( ' ||
> ' qry.pk, ' ||
> ' qry.data ' ||
> ' ) ' ;
> execute immediate
> dsql
> using
> 1 ;
> end ;
> /
> *************************************************************
>
> SQL> @p
> declare
> *
> ERROR at line 1:
> ORA-01008: not all variables bound
> ORA-06512: at line 31
>
> **************************************************************
> *** now I do create the following file and call it p2.sql. ***
> *** the difference is in the "using" clause only ***
> **************************************************************
> declare
> dsql varchar2(32767) ;
> begin
> dsql :=
> ' merge into ' ||
> ' y tbl ' ||
> ' using ( ' ||
> ' select ' ||
> ' x.pk, ' ||
> ' x.data ' ||
> ' from ' ||
> ' x ' ||
> ' where ' ||
> ' x.pk = :1 ' ||
> ' ) qry ' ||
> ' on ( ' ||
> ' tbl.pk = qry.pk ' ||
> ' ) ' ||
> ' when matched then ' ||
> ' update ' ||
> ' set ' ||
> ' tbl.data = qry.data ' ||
> ' when not matched then ' ||
> ' insert ( ' ||
> ' tbl.pk, ' ||
> ' tbl.data ' ||
> ' ) values ( ' ||
> ' qry.pk, ' ||
> ' qry.data ' ||
> ' ) ' ;
> execute immediate
> dsql
> using
> 'why do I need this?',
> 1 ;
> end ;
> /
> *************************************************************
>
> SQL> @p2
>
> PL/SQL procedure successfully completed.
>
> If I increase the number of "real" bind variables by 1,2,3,... I have to
> increase the number of "bogus" bind variables by 1,2,3,....
>
> Bug or feature? :)
>
> Kind regards,
>
> --
> Cris Carampa (spamto:cris119_at_operamail.com)
> "Welcome to the new world order
> Families sleepin' in their cars in the southwest
> No home no job no peace no rest" (Bruce Springsteen)
Received on Wed Feb 11 2004 - 04:57:19 CST