ORA-604 and ORA-904 Erors when Using Subquery Factoring in a Query

(Doc ID 276999.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 9.2.0.8
This problem can occur on any platform.
The problem affects all Oracle 9.2.0.X software releases.

Symptoms

When using subquery factoring with regular tables or object views, you may get ORA-604 and
ORA-904 errors from the query.

 For example:
SQL> connect SCOTT/TIGER
SQL> WITH c_emp AS(
SELECT LEVEL emp_level,empno,ename,job,mgr,sal,
SYS_CONNECT_BY_PATH(ename,'/') path_ename,
(SELECT m.sal FROM EMP m WHERE m.empno = e.mgr) sal_mgr
FROM EMP e
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
)
SELECT * FROM c_emp WHERE JOB = 'SALESMAN'
UNION
SELECT * FROM c_emp WHERE JOB = 'MANAGER'
/
(SELECT m.sal FROM EMP m WHERE m.empno = e.mgr) sal_mgr
*
ERROR at line 4:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "E"."MGR": invalid identifier



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms