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
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';