This discussion is archived
11 Replies Latest reply: Apr 23, 2013 5:07 PM by davidp 2 RSS

sql merge statement and versioned tables

152388 Newbie
Currently Being Moderated
Hi,

Does anyone know whether the "INSTEAD OF UPDATE/INSERT/DELETE ON" triggers (OWM_update_## etc.) on the views that replace the original table when versioning is enabled actually do fire, when the update/insert is issued from within a "MERGE INTO"-SQLstatement. In my case all the straight updates, inserts and deletes are correcty recorded in the ########_HIST view. The SQL MERGE normally provides a much simpler solution - but versioned tables/OWM apparanty do not comply with the logics of the SQL MERGE.

Any suggestions ??
  • 1. Re: sql merge statement and versioned tables
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Unfortunately the merge operation does not currently work with versioned tables. 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: sql merge statement and versioned tables
    152388 Newbie
    Currently Being Moderated
    Thanks,
    So it was as suspected !
  • 3. Re: sql merge statement and versioned tables
    692491 Newbie
    Currently Being Moderated
    Hi,

    2 follow-up questions:

    What is the best (means fastest running) workaround construction to a merge statement on version enabled tables ?
    Is a bulk merge possible resp. is there a workaround construction for it ?

    Regards,
    Martin
  • 4. Re: sql merge statement and versioned tables
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Without knowing the exact merge statement you are interested in, the general idea is to break the merge into its individual dml components(insert,update,delete), and then to construct a where clause to check for existence in the table to avoid errors or duplicating data. This will allow the instead of triggers defined on the view to fire as expected.

    Regards,
    Ben
  • 5. Re: sql merge statement and versioned tables
    davidp 2 Pro
    Currently Being Moderated
    I just encountered this - merge statements get ORA-01400: cannot insert NULL into (<owner>."<table>_LT"."VERSION") when inserting.
    This is a disaster and not documented at all in the "Workspace Manager Developer's Guide". Instead it says "application SQL statements for selecting, inserting, modifying, and deleting data continue to work in the usual way with version-enabled tables, although you cannot update a primary key column value in a version-enabled table."
    MERGE is an application SQL statement, and a very good high performance one.
    This is a serious limitation of Workspace manager, and definitely should be documented.
    This may be a show stopper for our proposed implementation of Workspace Manager.
  • 6. Re: sql merge statement and versioned tables
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Yes, that's the typical error message for a sql merge statement. We would like to support it, but due to the way the database implements it, it is not possible at this time. It should be possible to separate the merge into the individual dml components, as needed.

    Regards,
    Ben
  • 7. Re: sql merge statement and versioned tables
    davidp 2 Pro
    Currently Being Moderated
    Hi Ben,

    Thanks for responding.
    Where the merge statement is not doing an insert, it gives no error message, and I have demonstrated on 11.2.0.3 its updates
    a) Ignoring foreign key constraints
    b) updating LIVE when the merge is done within a child, giving LIVE inconsistent data.
    c) not retaining history even with a VIEW_WO_OVERWRITE table.
    I have added scripts showing this to my SR 3-7056467191

    I have two strong objections:
    1. This seems to have never been documented in the Oracle Manuals, White Papers, or My Oracle Support. That approaches being a deceptive marketing practice. This limitation should be clearly documented!
    2. There is no error message in these cases, so data integrity is lost if support people use MERGE without knowing it does not work correctly with Workspace Manager. If it raised an error (like Virtual Private Database used to raise ORA-28132 for merge) then at least the data would be protected.
  • 8. Re: sql merge statement and versioned tables
    davidp 2 Pro
    Currently Being Moderated
    Moving on, I am trying to re-write my Merge as an update and insert, but when I try the update on a versioned table, I get ORA-01733: virtual column not allowed here:
    create table suppliers (supplier varchar2(10) not null, postcode varchar2(10), constraint suppliers_pk primary key (supplier));
    create table sup_data (supplier varchar2(10) not null, new_postcode varchar2(10), constraint sup_data_pk primary key (supplier));
    insert into suppliers values ('NORTH', null);
    insert into sup_data values ('NORTH', '3000');
    commit;
    update (select d.postcode, s.new_postcode from suppliers d join sup_data s on d.supplier = s.supplier)
    set postcode= new_postcode;
    rollback;
    exec DBMS_WM.EnableVersioning ('suppliers', hist=> 'VIEW_WO_OVERWRITE');
    update (select d.postcode, s.new_postcode from suppliers d join sup_data s on d.supplier = s.supplier)
    set postcode= new_postcode;

    set postcode= new_postcode
    ***
    ERROR at line 2:
    ORA-01733: virtual column not allowed here
  • 9. Re: sql merge statement and versioned tables
    davidp 2 Pro
    Currently Being Moderated
    To clarify my previous post, I can use an updatable inline join view successfully while the table is not version enabled.
    When I version enable the table, the exact same update fails with ORA-01733.
    I need to do a big update - there could be 10,000 rows with 80 columns being updated, and I will be doing this for 200 tables.
  • 10. Re: sql merge statement and versioned tables
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    We'll document the lack or MERGE support in the future. From Workspace Manager's perspective there is little that can be done to prevent the merge being rewritten to dmls on the base table. Without the enhancement you listed in the other thread (bug 7829229), it's not really feasible to avoid the problem other than making sure not to use sql merge for versioned tables.

    In terms of the ORA-1733 error, you can rewrite the query to use an exists. The following should work:

    SQL> update suppliers old
    set old.postcode = (select new.new_postcode from sup_data new where new.supplier = old.supplier)
    where exists (select 1
    from sup_data new
    where new.supplier = old.supplier);

    Regards,
    Ben
  • 11. Re: sql merge statement and versioned tables
    davidp 2 Pro
    Currently Being Moderated
    Thanks for replying Ben.

    I need to update 80 columns. I have learnt that you can set multiple columns in the update SET clause as (c1, c2, c3...) = (select c1, c2, c3... ) e.g.
    update suppliers d
    set (postcode, v1, v2, v3) = (select s.new_postcode , s.nv1, s.nv2, s.nv3 from sup_data s where d.supplier = s.supplier)
    where exists (select * from sup_data s where d.supplier = s.supplier)
    That does the update I want, without being too horrible. It also is doing full scans of the suppliers_LT table in my tests, which seems inefficient when I am only updating 3 rows out of 12,000.