Wednesday, April 15, 2015

Materialized View Limitation on 12.2.x, Can be checked via GLOBAL STANDARDS COMPLIANCE CHECKER

The Materialized View is a non-editioned object type, and therefore a materialized view cannot directly reference editioned objects. To avoid this
limitation, Oracle E-Business Suite Online Patching technology implements a new Effectively Editioned Materialized View compound object. Application developers
create and maintain the Materialized View Definition (query) in an ordinary view. The Online Patching technology then automatically maintains a
corresponding Materialized View Implementation that is legal for editioned databases.

MV Definition Standards:
A Materialized View Name must be unique within the first 29 bytes.
A Materialized View Definition must be stored in an ordinary view called MV_NAME||'#'.
Create or replace the Materialized View Definition as an ordinary view calledmv_name||'#'.
Test the MV Definition for accuracy before generating the MV Implementation.

For example:
create or replace view FND_EXAMPLE_MV# as select ... ;
select * from fnd_example_mv#;
The Materialized View Implementation is automatically generated from the MV Definition using the AD_ZD_MVIEW.UPGRADE procedure.
The syntax is exec ad_zd_mview.upgrade(<MV_OWNER>, <MV_NAME>)
Do not attempt to directly create or replace the Materialized View Implementation Query. To recreate an MV Implementation, call the
AD_ZD_MVIEW.UPGRADE procedure.

A Materialized View Definition must specify a column alias for each item in the select list.

In this scenario how we need to make this work for custom materialized views?

2 comments:

  1. Hello,

    We are in the process of upgrading to R12.2 from R12.1.3

    This is a question regarding what needs to be done to the existing custom materialized views BEFORE upgrading to 12.2.

    I think the steps which you have mentioned above are for an existing R12.2 ebs database.

    What to do for to prepare custom mviews before the upgrade process to R12.2 ?

    Thanks


    ReplyDelete

  2. Please go through the section "Enabling Online Patching" in below doc.
    https://docs.oracle.com/cd/E26401_01/doc.122/e48839/T579510T580325.htm

    ReplyDelete