11 Replies Latest reply: Apr 23, 2013 7:07 PM by davidp 2 RSS

    sql merge statement and versioned tables

    152388
      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-Oracle
          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
            Thanks,
            So it was as suspected !
            • 3. Re: sql merge statement and versioned tables
              rm.meyer
              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-Oracle
                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
                  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-Oracle
                    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
                      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
                        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
                          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-Oracle
                            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
                              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.