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.

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.

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.

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.

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.

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.

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.

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);