My Oracle Support Banner

How to Call DBMS_MVIEW.EXPLAIN_MVIEW Procedure with a Query Located in a Text File (Doc ID 1500819.1)

Last updated on MARCH 11, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Goal

You may want to create a fast refresh materialized view, but when you do so you get ORA-12015: cannot create a fast refresh materialized view from a complex query.

The next step in troubleshooting is often to run the DBMS_MVIEW.EXPLAIN_MVIEW procedure for the mview definition query to get more information about the cause.

Feeding a large query to a variable using string concatenation is usually troublesome and prone to errors. This note provides an alternative way by placing the mview definition query into a text file, and calling a wrapper procedure that reads the query into a CLOB and calls the explain_mview.

Note the following code is purely an example, for the sole purpose of demonstrating the idea to pass the query via a text file to the explain_mview procedure via CLOB.

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.