SQL Developer Cannot Generate SQL Script For Materialized Views In Other Schema (Doc ID 2698168.1)

Last updated on SEPTEMBER 20, 2022

Applies to:

Oracle SQL Developer - Version 20.2 and later
Information in this document applies to any platform.

Symptoms

NOTE: The example(s) provided are for demonstration purposes only and represents fictitious sample (based upon made up data used in the Oracle Demo instance). Any similarity to actual data, web sites and / or persons, living or dead, is purely coincidental and not intended in any manner.

Environment

------------------
SQL Developer 20.2
Database 12.1.0.2.0
Windows 7 64-bit

Problem
----------------
SQL Developer cannot generate SQL script for materialized views in other schema

Noticed that SQL Developer 20.2 cannot generate SQL script for materialized views in other schema - when you open a materialized view in another schema and click on the SQL tab, you just get a blank window.

In previous versions, SQL Developer would produce a comment saying "-- Unable to render MATERIALIZED VIEW DDL for object [{schema}.{name}] with DBMS_METADATA attempting internal generator." on top of the window, followed by a (poorly formatted) SQL script for creating the materialized view, but not in version 20.2.

Steps per customer
---------------------------
To easily reproduce the issue in a database that has HR and SCOTT schemas, you can do the following:
  > connect as a DBA
  > grant Select Any Dictionary to Scott;
  > connect HR/HR
  > create materialized view MV_EMP as select * from EMP;
  > (in SQL Developer 20.2) connect as Scott
  > open MV_EMP materialized view in HR schema in the left (navigation) panel
  > click on the SQL tab in the right (detail) panel and see what you get
  > you can then repeat the last SQL Developer steps in an earlier release of SQL Developer, say 19.2, and see the difference
  > finally, to clean up, connect as a DBA and "revoke Select Any Dictionary from Scott"