My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
  Cause
  Solution
  References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

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