My Oracle Support Banner

On 11.2 SDO_ANYINTERACT fails with ORA-918 - "column ambiguously defined" error (Doc ID 1492899.1)

Last updated on SEPTEMBER 21, 2012

Applies to:

Oracle Spatial - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

After upgrade from 11.1.0.7 to 11.2.0.3 certain SQLs returns ORA-918.

declare
dmu mdsys.sdo_geometry := SDO_GEOMETRY(2003, 27700, NULL,
SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(396834.329, 402940.923,
396908.932, 403014.924));
begin
   for c1 in (
   SELECT
SITE.FID,
SITE.FEATURE_GROUP,
SITE.BASEFORM_FORM,
SITE.BASEFUNCTION_COMPS_XML,
SITE.HEIGHT_CAPTURE_METHOD,
SITE.TEXT_CARTOGRAPHY_COMPS_XML
FROM
ADE_FSF SITE
WHERE
SITE.FID IN
(
(
SELECT /*+USE_NL(rf) ORDERED */
RF.FUNCTIONAL_SITE_FID
FROM
ADE_TAF TA,
ADE_FDTR RF
WHERE
SDO_ANYINTERACT(TA.LOCATION_GEOMETRY, dmu) = 'TRUE' AND
RF.TOPOGRAPHY_AREA_FID = TA.FID
)
UNION ALL
(
SELECT
FS.FID
FROM
ADE_FSF FS
WHERE
SDO_ANYINTERACT(FS.LOCATION_GEOMETRY, dmu) = 'TRUE'
)
UNION ALL
(
SELECT
FS.FID
FROM
ADE_FSF FS, ADE_TAF TA
WHERE
TA.FEATURE_GROUP = 329 AND                              --
Carto+Landform+Normal
TA.BASEFORM_FORM <> 28 AND                              -- We can ignore
Artifical Cartographic Slope
TA.BASEFORM_FORM <> 265 AND                             -- We can ignore
Cartographic Cliff
TA.BASEFORM_FORM <> 298 AND                             -- We can ignore
Coastal Slope
SDO_ANYINTERACT(TA.LOCATION_GEOMETRY, dmu) = 'TRUE'    -- Landform areas that
interact with the DMU
AND SDO_ANYINTERACT(FS.LOCATION_GEOMETRY,
SDO_CS.MAKE_2d(TA.LOCATION_GEOMETRY)) = 'TRUE'
-- Func sites that interact with those landform areas
                            )
                        )
)
loop
null;
end loop;
end;
/
declare
*
ERROR at line 1:
ORA-00918: column ambiguously defined
ORA-06512: at line 4

Changes

Database has been upgraded from 11.1.0.7 to 11gR2.

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
Changes
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.