Skip to content
Home » How to Create Materialized View in Oracle Apps 12.2.5 (R12)

How to Create Materialized View in Oracle Apps 12.2.5 (R12)

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.

exec ad_zd_mview.upgrade('APPS','XX_TEST_MV');

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%';

materialized view in Oracle R12

While working on this example, I faced the below issue. Maybe something I mentioned was wrong in the View statement.

exec ad_zd_mview.upgrade(‘APPS’,’XX_TEST_MV’)
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
corrective action.

What I did, was simply opened the new session and ran the script again and it worked. Strange.

Summary

We have explained creating a materialized view in R12. I hope you found it helpful. Please like and subscribe.

Reference

  • How to create Materialized View in E-Business Suite (Doc ID 2793260.1)