ORA 604 error (ORA-00918) occurred at recursive SQL level <num>

Resolving The Problem

There is a bug in Oracle version 9.2.0.0 to 9.2.0.4.

Oracle throws ORA-604 error while running the query, which has, EXISTS clause in it. Some of our Yantra query uses this clause. When these queries runs, Oracle throws 'ambiguous column error' due to this bug. Example Yantra query is given below.

The ORA-604 error (ORA-00604: error occurred at recursive SQL level 1 ORA-00918: column ambiguously defined) can occur for the internal SQL created for temporary tables if an index join access path has been chosen. This is most likely to happen in star transformation. This problem can occur on any platform.

The work around is, to set the initialization parameter star_transformation_enabled=temp_disable. Or install 9.2.0.5 which brings it's own set of problems.

Also refer the Oracle bug ID: 268920.1 in metalink.

Sample Yantra Query:
====================
SELECT /*YANTRA*/ YFS_ORDER_HEADER.*
FROM YFS_ORDER_HEADER YFS_ORDER_HEADER
WHERE ( ( YFS_ORDER_HEADER.ENTERPRISE_KEY ='OPNA' )
AND ( YFS_ORDER_HEADER.ORDER_TYPE = 'REORDER' )
AND ( YFS_ORDER_HEADER.BUYER_ORGANIZATION_CODE = '222' )
AND ( YFS_ORDER_HEADER.SELLER_ORGANIZATION_CODE = 'A3236' ) )
AND ( ORDER_HEADER_KEY
IN
(SELECT ORDER_HEADER_KEY
FROM YFS_ORDER_LINE
WHERE YFS_ORDER_LINE.ORDER_HEADER_KEY = YFS_ORDER_HEADER.ORDER_HEADER_KEY
AND ( ( YFS_ORDER_LINE.ITEM_ID = 'RM2122KT' ) ) ) )
AND
EXISTS (SELECT ORDER_RELEASE_STATUS_KEY
FROM YFS_ORDER_RELEASE_STATUS
WHERE STATUS_QUANTITY > '0'
AND YFS_ORDER_RELEASE_STATUS.STATUS <> '1400';
AND ( ( YFS_ORDER_RELEASE_STATUS.STATUS >= '1100'
AND YFS_ORDER_RELEASE_STATUS.STATUS <= '1100' ) ) <br>AND YFS_ORDER_HEADER.ORDER_HEADER_KEY = YFS_ORDER_RELEASE_STATUS.ORDER_HEADER_KEY)