Skip to Main Content
  • Questions
  • Error using DBMS_MVIEW.EXPLAIN_REWRITE

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Iudith.

Asked: November 01, 2021 - 11:26 am UTC

Last updated: January 19, 2022 - 1:50 am UTC

Version: 19.8

Viewed 1000+ times

You Asked

Hello All,

The problem I raised uses a scenario from one of the quizzes played recently on Devgym.

I try to use DBMS_MVIEW.EXPLAIN_REWRITE for a materialized view in LiveSQL
and for some reason, I encounter an error for both the table and the array version.

- For the table version, the following error is raised:

ORA-00942: table or view does not exist

- For the array version, another error is raised:

ORA-01405: fetched column value is NULL


I created a LiveSQL script for this sample scenario:

https://livesql.oracle.com/apex/livesql/s/mkiuyitnlmnrp2rgogv2h4x60


Thanks a lot in advance if you can investigate this issue.

Best Regards,
Iudith Mentzel


with LiveSQL Test Case:

and Connor said...

Please log feedback with the LiveSQL team on the first error (ORA-942). That is likely to be a privilege issue.

The array alternative should work but you need to initialise the varray

SQL> set serverout on
SQL> declare
  2
  3    l_data   sys.REWRITEARRAYTYPE := sys.REWRITEARRAYTYPE();
  4    l_rc     sys_refcursor;
  5
  6    l_message     varchar2(2000);
  7
  8  -- the query
  9    querytxt VARCHAR2(2000) :=
 10    'select count ( distinct colour ) colour#,
 11            count ( distinct shape ) shape#
 12     from   qz_bricks';
 13
 14
 15  -- a target mview
 16    mv  VARCHAR2(100) := 'QZ_BRICK_SUMMARY';
 17
 18  begin
 19
 20           DBMS_MVIEW.EXPLAIN_REWRITE (querytxt, mv, l_data);
 21
 22           open l_rc for
 23           select message
 24           from table( cast( l_data as sys.REWRITEARRAYTYPE ) )
 25           order by sequence;
 26
 27           loop
 28              fetch l_rc into l_message;
 29              exit when l_rc%notfound;
 30
 31              dbms_output.put_line(l_message);
 32           end loop;
 33
 34           close l_rc;
 35  end;
 36  /
QSM-01150: query did not rewrite
QSM-01066: materialized view, QZ_BRICK_SUMMARY, does not have the column, COLOUR, from query

PL/SQL procedure successfully completed.



Addenda - the second one failed for me too in LiveSQL - please log a bug in LiveSQL

Rating

  (3 ratings)

Comments

Interesting ... :)

Iudith Mentzel, November 02, 2021 - 11:32 am UTC

Hello Connor,
Thanks a lot for your so fast answer :)

Regarding the array version, I was "driven away" as the similar procedure DBMS.EXPLAIN_VIEW, which also uses an a predefined array, does not require array initialization :)

Regarding the table version, the behavior is strange:

I did create the required results table REWRITE_TABLE
in my own (temporary in LiveSQL) schema, so, if it is indeed a privilege problem, then maybe this table is required to reside in some other schema, like SYS, though such a requirement is not documented.
If this is indeed the case, then maybe it is a bug that should be checked and corrected ?

I think that the behavior should be similar to that of using a PLAN_TABLE, which can reside in any schema.

Cheers & Best Regards,
Iudith





Connor McDonald
November 09, 2021 - 2:55 am UTC

I suspect its just an issue in how LiveSQL processes schemas (in order to preserve security)

Problem in LiveSQL, after everything worked ok !!!

Iudith Mentzel, January 10, 2022 - 2:03 pm UTC

Hello All and Happy New Year 2022,

A very strange thing happened :( ...

Just a few weeks after correcting the problem of using DBMS_MVIEW.EXPLAIN_REWRITE in LiveSQL,
and after eveything was working ok, attempting to execute now the very same corrected code again,
does not work anymore ... this time, with a different error:

declare 
          
  l_data   sys.REWRITEARRAYTYPE := sys.REWRITEARRAYTYPE(); 
  l_rc     sys_refcursor; 
 
  l_message     varchar2(2000); 
 
-- the query 
  querytxt VARCHAR2(2000) :=  
  'select count ( distinct colour ) colour#, 
          count ( distinct shape ) shape# 
   from   qz_bricks'; 
 
 
-- a target mview 
  mv  VARCHAR2(100) := 'QZ_BRICK_SUMMARY'; 
 
begin 
  
         DBMS_MVIEW.EXPLAIN_REWRITE (querytxt, mv, l_data); 
 
         open l_rc for 
         select message 
         from table( cast( l_data as sys.REWRITEARRAYTYPE ) ) 
         order by sequence; 
 
         loop 
            fetch l_rc into l_message; 
            exit when l_rc%notfound; 
 
            dbms_output.put_line(l_message); 
         end loop; 
 
         close l_rc; 
 
end; 
/

ORA-00942: table or view does not exist 
ORA-06512: at "SYS.DBMS_XRWMV", line 120
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 4140
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 273
ORA-06512: at line 20
ORA-06512: at "SYS.DBMS_SQL", line 1721



This is the same error that I received initially, when trying to use the "table" version of DBMS_MVIEW.EXPLAIN_REWRITE !

Now it also appears when using the "array" version, that previously worked ok !!!

One possible guess is that, maybe a SYS.REWRITE_TABLE is suddenly used internally by both the "table" and the "array" version, and this table either does not exist or, somehow strangely, it requires privileges for the package calling user.

Can you please help me to figure out which privilege is missing in LiveSQL, so that I could report it to the LiveSQL team for correction?

Cheers & Best Regards,
Iudith

Connor McDonald
January 19, 2022 - 1:50 am UTC

WIll pass it on to the livesql folks for comment.

Update Jan 2022:

Turns out there is a db bug with this. 33764772 - DBMS_MVIEW.EXPLAIN_REWRITE USES SESSION USER FOR NAME RESOLUTION

As a workaround, you can include the schema name in the query, e.g.

-- the query
querytxt VARCHAR2(2000) :=
'select count(*) from '||sys_context('userenv','current_user'||'.qz_bricks';

BUG 33764772

Iudith Mentzel, January 19, 2022 - 4:52 pm UTC

Hello Connor,

Thank you so much, the ARRAY version now works again correctly in LiveSQL.

The TABLE version does still has the problem, probably because the SESSION USER is also used for the resolution of the REWRITE_TABLE, over which the package caller does NOT have any control.

Unfortunately, I do not have access to the BUG itself, but I want to hope that it will be corrected and all these aspects will be considered.

Cheers & Best Regards,
Iudith Mentzel

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.