Home » SQL & PL/SQL » SQL & PL/SQL » Writable materialized view with a custom column (merged) (Oracle 10.2.0.4)
Writable materialized view with a custom column (merged) [message #434841] Fri, 11 December 2009 03:49 Go to next message
adry1
Messages: 1
Registered: December 2009
Junior Member
Hello folk!

I will really appreciate your help with the following:

I need to have a Materialized View. It should be 'Fast'-refreshed on demand. In addition to columns from tables which form the basis for MV, I need to have there an additional column which stores some operational data and must be updatable.

So, I create the MV as:
CREATE MATERIALIZED VIEW schema.test_view REFRESH FAST ON DEMAND FOR UPDATE AS
SELECT column_a, 'Initial Value' AS custom_column FROM my_table


Then, some external logic makes updates for the custom_column like
update test_view set custom_column = 'Done'


But as a refresh for the MV is being made, I get for custom_column its initial value ('Initial Value').

In documentation, it is stated:
Quote:
Users can perform DML operations on a writeable materialized view, but if you refresh the materialized view, then these changes are not pushed back to the master and the changes are lost in the materialized view itself.


So, no solution for my case..?

Thank you very much!

Andrey
Re: Writable materialized view with a custom column (merged) [message #434854 is a reply to message #434841] Fri, 11 December 2009 04:43 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could try this:
1) Create a normal MView without the updatable column.
2) Create a View on the Mview and the column that you want to update
3) Create an Instead of Update trigger in the View to push updates to that column back to it's master table.
Previous Topic: How to load data from an excel file to a table
Next Topic: i had a doubt
Goto Forum:
  


Current Time: Tue Feb 11 13:39:24 CST 2025