Materialized view log for nested views

[Former Member]

Hello,

I want to use nested materialized views with "on commit refresh" (oracle).

Like this : Materialized Views

I'm unable to design this in power designer because it is impossible to add materialized view logs on materialized views (only for tables ? Is this feature missing ?).

What is the solution ?

I tried to do a script for the manual creation of logs in the "begin script" tab of my materialized view but, for me, begin/end scripts of materialized views are not added to the final generated script. In my case, it works only for tables (bug ?).

What is the solution please ?

Best regards,

Ludovic.

[GeorgeMcGeachie · Accepted solution]

The long term solution is to raise a support case, and get SAP to add this to the standard Oracle DBMS definition. In the short term, it's easy to add it yourself.

Take a copy of the standard Oracle database definition (probably c:\program files\Sybase\powerdesigner 16\resource files\dbms\ora12c.xdb), and use the 'Change Current Database' option on the Database menu to attach the copy to your PDM.

Now you can edit the DBMS definition (via 'Edit Current Database' on the Database menu):

The feature you want is already available for tables, so you just need to copy certain parts of the profile for Table to the profile for the Materialized View stereotype in the profile for View. You can do this by dragging them while holding down the Ctrl key. You need to copy the following:

Extended Attribute: MaterializedViewLog

Form: Oracle

Methods: CreateMaterializedViewLog and RemoveMaterializedViewLog (you may choose to change the wording of any messages that refer to Tables)

Click on OK, and save the file. You should now be able to create Materialized View Logs for Materialized Views, and they will be included in your DDL in the same way as they are for Tables.

[Former Member · Accepted solution]

Thank you very much for your solution George, it's perfect !

An other question : Where did you found ora12c.xdb ??

[GeorgeMcGeachie · Accepted solution]

Oracle 12c support was introduced in PD 16.5 SP03. If you're running an earlier version of PD, I expect SAP would supply the xdb file if you request it.

[Former Member · Accepted solution]

Thank you !