2

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.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • Something is going wrong at lines 5746 and 8333 in `DBMS_METADATA` (probably a call at 8333 cascading up to the caller at 5746). Only Oracle knows what it's trying to do at that point in the code. It works for me in 12.1.0.2. Joining `v$open_cursor` for my sid with `v$sql` and `dba_objects` to list cursors for objects matching `DBMS_METADATA%`, I didn't see anything from those lines. Maybe they fixed it in 12.1.0.2. – William Robertson May 16 '17 at 12:17
  • Interesting. However, I would not have tables and views with the same name. Name your table `sometest` and your MV `sometest_mv`. – unleashed May 16 '17 at 12:18
  • Appears to a bug, Ben. MV and a table that MV is built on must have same names. So it's not the cause of the problem. There is a similar bug( #15922287 registered, `ORA-01427` is raised when you try to get DDL of an editions-enabled view - fixed in 12.1.0.2.0). Just tested on 12.1.0.2.0 - no errors. – Nick Krasnov May 16 '17 at 12:33

1 Answers1

0

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.
Giova
  • 1,137
  • 1
  • 9
  • 17