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:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and laterInformation 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 |