This article explains How to Create Materialized View in Oracle Apps 12.2.5 (R12) where online patching or editioning is enabled.
You can refer Oracle Guide to know the proper definition of Materialized view. Here I will simply put is as,
Materialized view in Oracle are the database objects based on SQL Queries. Unlike normal view MV actually contains the data.
The generic statement to create a Materialize View in Oracle Database is as below,
CREATE MATERIALIZED VIEW viewname [options] AS SELECT .....
While working on Oracle Apps 12.2.5 where editioning or online patching is enabled, you need to use a different approach to create a materialized view.
Create Materialized View in Oracle Apps 12.2.5 (R12)
You need to create a logical view and then use ad_zd_mview. upgrade script to create a materialized view. Internally oracle creates required edition materialized view.
Let’s see it with example.
Create Logical View
This is the first step. You need to create a logical view. It is a normal view only suffixed by the # character.
CREATE OR REPLACE VIEW apps.xx_test_mv# AS SELECT qlh.rowid qlhrow_id , qlb.rowid qlbrow_id, name FROM qp_list_headers_tl qlh, qp_list_headers_b qlb WHERE qlh.list_header_id = qlb.list_header_id ;
Run ad_zd_mview. upgrade
Now run ad_zd_mview script as shown below. The first parameter is the schema name and second the view name without # suffix.
Now, let’s see objects created by Oracle in backend.
SELECT * FROM dba_objects WHERE object_name LIKE 'XX_TEST_MV%';
While working on this example, I faced the below issue. Maybe something I mentioned wrong in View statement.
Error report –
ORA-24256: EXPAND_SQL_TEXT failed with ORA-00933: SQL command not properly ended
ORA-06512: at “SYS.DBMS_UTILITY”, line 1538
ORA-06512: at “APPS.AD_ZD_MVIEW”, line 209
ORA-06512: at “APPS.AD_ZD_MVIEW”, line 1283
ORA-06512: at “APPS.AD_ZD_MVIEW”, line 1628
ORA-06512: at “APPS.AD_ZD_MVIEW”, line 714
ORA-06512: at “APPS.AD_ZD_MVIEW”, line 1130
ORA-06512: at “APPS.AD_ZD_MVIEW”, line 1885
ORA-06512: at line 1
24256. 00000 – “EXPAND_SQL_TEXT failed with %s”
*Cause: See the string parameter at the end of the message.
*Action: See the string parameter at the end of the message and take
What I did, simply opened new session and ran the script again and it worked. Strange.
We have explained creating a materized view in R12.
I hope you found it helpful. Please like and subscribe.