Get the DDL of a materialized view on a pre-built table - getting single-row subquery

Question

I have a materialized view on a pre-built table with the same name. When attempting to use DBMS_METADATA.GET_DDL() to obtain the DDL of the materialized view I'm getting ORA-01427: single-row subquery returns more than one row from within DBMS_METADATA. Here's a minimal example:

SQL> create table mv_test as select * from dual;

Table created.

SQL> create materialized view mv_test
  2   on prebuilt table
  3   refresh force on demand as
  4  select *
  5    from dual;

Materialized view created.

SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MV_TEST', user) from dual;
ERROR:
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_METADATA", line 5746
ORA-06512: at "SYS.DBMS_METADATA", line 8333
ORA-06512: at line 1



no rows selected

SQL>

The same error occurs when assigning directly to a variable in PL/SQL and when using SQL within PL/SQL.

This isn't particularly optimal... I assume the error is occurring because the table and the materialized view have the same name and GET_DDL() is returning more than one row into my SELECT.

I do only want one row - the row for the materialized view. I have specified this in the call to GET_DDL(). I do not want to return the DDL for the table.

Some further points:

• The list of object types available in DBMS_METADATA does not make a distinction between a materialized view and a materialized view on a pre-built table
• Neither the table nor the materialized view is editioned
• Oracle version 12.1.0.1

How can I get only the DDL of the materialized view.

Answer 1

there are some bugs about this error raised against materialized view, i think the the same name on table and materialized view does not matter. I think you have to:

• look for the error on Oracle Metalink;
• investigate against Oracle's notes on Metalink;
• if necessary open and manage a SR to the Oracle Support;
• find a workaround/patch for this bug/problem.