This discussion is archived
3 Replies Latest reply: Apr 18, 2013 5:52 PM by davidp 2 RSS

Using merge statement on version-enabled table with valid time support

user93128 Newbie
Currently Being Moderated
Hi,
just a simple question: is it possible to use a merge statement on a version-enabled table with valid time support?

For the following test I am getting an ORA-01733.
(t1 is the version-enabled table with valid time support. t1 and t2 have the same structure: two fields: CODE (pk) and NAME)

merge into <table1> t1
using <table2> t2
on (t1.CODE = t2.CODE)
when matched THEN update set t1.NAME = t1.NAME
where t1.NAME <> t2.NAME;

Thanks,
  • 1. Re: Using merge statement on version-enabled table with valid time support
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    The sql merge operation does not currently work with versioned tables, either with or without validtime. The optimizer translates the merge into insert/update statements on the underlying _LT table. As a result, the instead of triggers that are created during enableversioning are never fired.

    Regards,
    Ben
  • 2. Re: Using merge statement on version-enabled table with valid time support
    ⁸⁷Rb-⁸⁷Sr Newbie
    Currently Being Moderated
    Ben Speckhard wrote:
    Hi,

    The sql merge operation does not currently work with versioned tables, either with or without validtime. The optimizer translates the merge into insert/update statements on the underlying _LT table. As a result, the instead of triggers that are created during enableversioning are never fired.

    Regards,
    Ben
    Hi Ben,



    Can we found this information in the documentation?

    I was surprised when I updated LIVE workspace
  • 3. Re: Using merge statement on version-enabled table with valid time support
    davidp 2 Pro
    Currently Being Moderated
    I cannot find any reference to this in the documentation, but I have had it confirmed by Oracle Support via an SR:

    "OWM does not support the sql merge statement due to the instead of triggers defined on the view. The optimizer performs the dmls on the base _LT table, which incorrectly skips the instead of triggers. A bug can be filed against the database if needed, but there's nothing that can be done at the [current] time from OWM's perspective. Typically, a merge can be decomposed into the individual sql statements, which is supported."

    Bug 7829229 : USING "MERGE STATEMENT" ON "COMPLEX VIEW" WITH INSTEAD OF TRIGGER is an enhancement request from 2009 that asks for MERGE to work with INSTEAD OF triggers. It needs people to submit SR's in support of it.
    I have not seen any documentation reference to MERGE not working on INSTEAD OF triggers, but clearly they don't.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points