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

Last updated on FEBRUARY 22, 2024

Applies to:

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

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