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

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

You need to create a logical view and then use ad_zd_mview. upgrade script to create a materialized view. Internally oracle creates the required edition materialized view.

Let’s see it with an example.

1. 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 ;

2. Run ad_zd_mview. upgrade

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 # suffix.

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

Now, let’s see objects created by Oracle in the backend.

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 wrong in 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, simply opened a 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.