Re: MERGE INTO without WHEN MATCHED, possible? (FOOD METAPHOR)

From: Volker Apelt <blackhole_at_notformail.invalid.de>
Date: 24 Sep 2002 11:05:15 +0200
Message-ID: <lgelbju59w.fsf@mu.biosolveit.local>

Martin Doherty <martin.doherty_at_oracle.com> writes:
> > Ouch, of course,
> > (Volker, takeing of the tomates from his eyes)
>
> Fascinating expression, I must remember it. I once spoke to a Russian friend
> about someone pulling the wool over my eyes, and he told me that in Russia
> they say "Don't go putting spaghetti on my ears".
>

:-)
How do you say it in english?

But, back on topic, while playing with the MERGE INTO expression I found a strange behaviour with bound variables.

Here is the documentation.

For the records: A case of ghost error messages with MERGE INTO.

Oracle 9i on Linux:  

Given two tables XXX and YYY with similar temporary tables temp_XXX and temp_YYY. temp_* tables have non of the constraints of the original tables. (temporaries have no primary keys, no referential integrity, no NOT NULL fields and such)

Here is the query in under observation:

---

MERGE INTO temp_XXX t_a

    USING ( select

            ID_XXX, field_A, field_B, field_C, field_D, ID_YYY
            from XXX
            where
               field_A = :1  and field_B = :2 and
               field_C = :3  and field_D = :4  and
               ID_YYY
                = ( SELECT ID_YYY FROM YYY WHERE NAME = :5 )
             ) a
    ON (a.field_A = t_a.field_A  and a.field_B = t_a.field_B and
        a.field_C = t_a.field_C  and a.field_D = t_a.field_D  
        and a.ID_YYY = t_a.ID_YYY )
    WHEN MATCHED THEN UPDATE
       SET  ID_XXX = a.ID_XXX
    WHEN NOT MATCHED THEN
       insert (field_A, field_B, field_C, field_D, ID_YYY) 
       VALUES(
         :6 , :7 , :8 , :9 , (SELECT ID_YYY FROM YYY WHERE NAME = :10 )
       )
---------------------------------

XXX.ID_XXX is created by trigger + sequence BEFORE INSERT. YYY.NAME is unique, so (SELECT ID_YYY FROM YYY WHERE NAME = :10 ) returns exactly one record.

Run with exactly 10 parameters
# 1 2 3 4 5 6 7 8 9 10 ('A', 'y', 'H', '213', 'zzz', 'A', 'y', 'H', '213', 'zzz') ORACLE complains:
DatabaseError: (1008, 'ORA-01008: not all variables bound')

With one additional bound parameter ORACLE complains: DatabaseError: (1036, 'ORA-01036: illegal variable name/number') .. as expected.

The official cause for ORA-01008 is (
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96525/e900.htm#1000657 ):

  ORA-01008 not all variables bound
  Cause: A SQL statement containing substitution variables was executed   without all variables bound. All substitution variables must have a   substituted value before the SQL statement is executed.   Action: In OCI, use an OBIND or OBINDN call to substitute the required values.

I've rewritten the statement to (see below) and it works with 5 parameters. ('A', 'y', 'H', '213', 'zzz')

---

MERGE INTO temp_XXX t_a

    USING ( select

            ID_XXX, field_A, field_B, field_C, field_D, ID_YYY
            from XXX
            where
               field_A = :1  and field_B = :2 and
               field_C = :3  and field_D = :4  and
               ID_YYY
                = ( SELECT ID_YYY FROM YYY WHERE NAME = :5 )
             ) a
    ON (a.field_A = t_a.field_A  and a.field_B = t_a.field_B and
        a.field_C = t_a.field_C  and a.field_D = t_a.field_D  
        and a.ID_YYY = t_a.ID_YYY )
    WHEN MATCHED THEN UPDATE
       SET  ID_XXX = a.ID_XXX
    WHEN NOT MATCHED THEN
       insert (field_A, field_B, field_C, field_D, ID_YYY) 
       VALUES(
        a.field_A, a.field_B, a.field_C, a.field_D, a.ID_YYY
       )
---------------------------------

Any resonable explanation ?
Are bound variables not allowed in WHEN part?

Thanks

Volker      Received on Tue Sep 24 2002 - 04:05:15 CDT