Materialized views are the database objects in Oracle based on SQL Queries. Unlike the normal view, MV actually contains the data.
The generic statement to create a Materialize View is,
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.
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 in Oracle Apps 12.2.5 onward where online patching or editioning is enabled.
Let’s see it with an example.
1. Create Logical View
First, 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 ;
2. Run ad_zd_mview. upgrade utility to Create Materialized View
Now, run the ad_zd_mview script as shown below. The first parameter is the schema name and the second is the view name without the # suffix.
Oracle internally creates a Table, Materialized View, and View. You can query the DBA_OBJECTS metadata table to see all these objects.
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 was wrong in the 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, was simply opened the new session and ran the script again and it worked. Strange.
We have explained creating a materialized view in R12. I hope you found it helpful. Please like and subscribe.
- How to create Materialized View in E-Business Suite (Doc ID 2793260.1)