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 220.127.116.11 and later
Information in this document applies to any platform.
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.
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