Wednesday 25 October 2017

           Materialized View (MV)
                 ---------------------------------

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(, )
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.
Failure to specify a column alias may cause the error
ORA-00998 "must name this expression with a column alias"
Example: change select sum(EMP.SALARY), ...
to select sum(EMP.SALARY) SUM_EMP_SALARY, ...
A Materialized View Query must not reference editioned PL/SQL functions.
If the MV definition references an editioned PL/SQL function, the MV
Implementation will fail to generate and the MV will be unusable.
For examples of replacing PL/SQL function calls with equivalent SQL in
Materialized Views, see: Examples of SQL Replacements for PL/SQL
Functions
A Materialized View should use 'REFRESH FORCE' instead of 'REFRESH
FAST'. The 'FORCE' option allows the MV to fall back to using a complete
refresh in situations where the fast refresh is not possible.
See: Oracle Database SQL Language Reference 11g Release 2 (11.2) for more
information on the "REFRESH FORCE" option.
If the MV Implementation content must be automatically refreshed after
patching, include the '/*AUTOREFRESH*/' comment tag in the MV Definition
query.
Do not specify the /*AUTOREFRESH*/ tag for large MVs that will take a
long time to refresh. For these cases use a concurrent program to
refresh the MV after patching cutover.
Example: create or replace view FND_EXAMPLE_MV# as select
/*AUTOREFRESH*/ ... ;
MV Usage Standards:
Do not assume that Fast Refresh is always possible. After an online patch,
Complete Refresh may be required. When refreshing a Materialized View, us
the 'FORCE' clause instead of 'FAST'.
See: Oracle Database SQL Language Reference 11g Release 2 (11.2) for more
information on the 'FORCE' option.
MV Dynamic DDL Standards:
Use AD_MV to execute Dynamic DDL for materialized views. Here is an
example of creating a materialized view using the AD_MV package:
--
-- Code Example: Create a materialized view using AD_MV interface.
--
-- Note:
-- when executed in the Run Edition, the MV is created immediately.
-- when executed in the Patch Edition, the MV is generated at CUTOVER.
--

begin
-- Create MV
ad_mv.create_mv('FND_EXAMPLE_MV',
'create materialized view FND_EXAMPLE_MV '||
' tablespace '||ad_mv.g_mv_data_tablespace||' '||
' build deferred refresh on demand as '||
'select /*AUTOREFRESH*/ '||
' upper(oracle_username) USERNAME '||
' , decode(read_only_flag,''C'',''pub'',''E'',''applsys'',''U'',''apps'')
USERTYPE '||
'from fnd_oracle_userid '||
'where read_only_flag in (''C'',''E'',''U'') ');
end;
-- End of Code Example.

MV Online Patching Compliance Standards:
Deliver Materialized View using XDF. (GSCC File.Sql.82)
Do not drop an existing materialized view until the Cleanup phase of patch
execution.
Do not attempt to upgrade, refresh or access a Materialized View
Implementation in the Patch Edition. Although the MV implementation is
visible to the Patch Edition, it continues to implement the Run Edition of
the definition until the cutover phase. MV implementations are
automatically regenerated as needed at the cutover phase.
If an online patch must manually refresh the MV Implementation
contents, submit a concurrent request to do the refresh. The concurrent
request will be run after cutover and therefore after the MV
implementation has been regenerated.
If the MV Definition specifies the /*AUTOREFRESH*/ comment tag, then
the MV Contents will be automatically refreshed whenever the MV
implementation is regenerated.
Do not drop an obsolete materialized view until the Cleanup phase of patch
execution.
For more information on materialized views, see: Oracle Database Concepts 11g
Release 2 (11.2).
For more information on using XDF (XML Definition File) features, see:
XML Definition File Utility.

Examples of SQL Replacements for PL/SQL Functions
-------------------------------------------------
To "Editions-enable" the APPS schema, non-Editionable objects must not depend
on Editionable objects (NE !-> E). To meet this requirement, the database
object development standards specify that Materialized Views (Materialized
Views, or MVs, are non-Editionable) must not call PL/SQL functions (which are
Editionable).
The examples below demonstrate how to replace frequently- used Oracle
Applications Technology PL/SQL function calls with an equivalent SQL in
Materialized Views. You may continue to call built-in PL/SQL functions such as
"upper()". fnd_profile.value() replaced with a SQL sub-select:
Before:
fnd_profile.value('MSC_HUB_REGION_INSTANCE')
After:
(select profile_option_value
from fnd_profile_option_values
where level_id = 10001
and (profile_option_id, application_id) =
(select profile_option_id, application_id
from fnd_profile_options
where profile_option_name = 'MSC_HUB_REGION_INSTANCE'))
Notes:
This replacement is valid ONLY in a Materialized View. For other uses of
fnd_profile.value(), continue using the normal PL/SQL call.
The general case for fetching profile option values is very complex, that
is why there is a PL/SQL package dedicated to doing it. But materialized
views results have to be valid in any context, so profile options
referenced in materialized views should only have site-level values, and
the replacement SQL only needs to support fetching the site level value.
This replacement SQL will only use the profile option value set at the site
level.
fnd_message.get_string() replaced with a SQL sub-select
Before:
fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED')
After:
(select substrb(REPLACE(message_text, '&&', '&'),1,2000)
from fnd_new_messages m, fnd_application a
where m.message_name = 'MSC_HUB_UNASSIGNED'
and m.language_code = 'US'
and a.application_short_name = 'MSC'
and m.application_id = a.application_id)
Notes:
This replacement is valid ONLY in a Materialized View. For other uses of
fnd_message.get_string(), continue using the normal PL/SQL call.
This replacement SQL will only retrieve the US language message text and is
not sensitive to any session language settings.
MV queries cannot contain a sub-SELECT within the main SELECT clause;
therefore, the replacement SQL is a bit trickier if the function call was
used in the MV SELECT clause.
Before:
select fnd_message.get_string('FND', 'CANCEL')
from dual
where 1=1
/
After:
select fmgs.result
from dual
, (select substrb(REPLACE(message_text, '&&', '&'),1,2000) result
from fnd_new_messages m, fnd_application a
where m.message_name = 'CANCEL'
and m.language_code = 'US'
and a.application_short_name = 'FND'
and m.application_id = a.application_id) fmgs
where 1=1
/
fnd_global.lookup_security_group() replaced with a SQL sub-select
Before:
fnd_global.lookup_security_group('INTEREST_STATUS', 279)
After:
(select nvl(max(lt.security_group_id), 0)
from fnd_lookup_types lt
where lt.view_application_id = 279
and lt.lookup_type = 'INTEREST_STATUS'
and lt.security_group_id in (
0,
to_number(decode(substrb(userenv('CLIENT_INFO'),55,1),
' ', '0',
null, '0',
substrb(userenv('CLIENT_INFO'),55,10)))))
Note: This replacement is valid ONLY in a Materialized View. For other uses of
fnd_global.security_group(), continue using the normal PL/SQL call.
Example of a Deferred Drop
Here is a code example of a deferred drop. This example is for a table:
REM dbdrv: sql ~PROD ~PATH ~FILE \
REM dbdrv: none none none sqlplus &phase=last \
REM dbdrv: checkfile:~PROD:~PATH:~FILE &un_fnd
-- Example logic to drop a table under online patching
--
-- Note: This script drops example table "APPLSYS.FND_EXAMPLE_TABLE".
-- To use this logic for another table, you must substitute "&un_fnd" with the
-- actual table owner token ("&un_"), and
-- "FND_EXAMPLE_TABLE" with the actual table name.
--
-- Usage
-- @TABLE_DROP_SCRIPT
drop synonym FND_EXAMPLE_TABLE;
drop view &1..FND_EXAMPLE_TABLE#;
exec ad_zd.load_ddl('CLEANUP', 'drop table &1..FND_EXAMPLE_TABLE')