Ora-936 When Using An Inline View (Doc ID 1146466.1)

Last updated on AUGUST 10, 2021

Applies to:

Symptoms

ORA-936 when running a query on the primary table
of materialized view with query rewrite enable.

TEST CASE
==========

CREATE TABLE XX
( AA CHAR(1)
, BB CHAR(1)
)
/

CREATE TABLE YY
( AA CHAR(1)
, BB CHAR(1)
)
/
CREATE TABLE ZZ
( AA CHAR(1)
, BB CHAR(1)
)
/

CREATE MATERIALIZED VIEW MV1
(AA)
 REFRESH COMPLETE
 ENABLE QUERY REWRITE
AS
SELECT T1.AA
 FROM
 (SELECT AA FROM YY
 WHERE YY.BB = (SELECT XX.BB FROM XX )
 ) T1
;


 SELECT T1.AA FROM
 (SELECT * FROM ZZ) T1,
 (SELECT * FROM XX ) T2
 where T1.BB = T2.BB
 /

SELECT T1.AA FROM
*
ERROR at line 1:
ORA-00936: missing expression


No errors when set query_rewrite_enabled = false

SELECT T1.AA FROM
(SELECT * FROM ZZ) T1,
(SELECT * FROM XX ) T2
where T1.BB = T2.BB
/

no rows selected

set query_rewrite_enabled = false

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.