Error using sde.ST_INTERSECTION in Oracle

[DavidO_Hara]

We have Arc 10.1 SDE geodatabase install in Oracle 11g.  I have two feature classes created with ST geometry.  I have the following select statement:
SELECT nfslandunitid,nfslandunittype,nffid,nfslandunitname,'Alabama',region,
       sde.ST_INTERSECTION(ru.shape,st.shape)
FROM EDW_ALPGPU_ST st, edw_nfslandunit_st ru
WHERE ru.objectid = 270
AND st.objectid = 3666;

I get the following error:
[1]: ORA-06530: Reference to uninitialized composite
[1]: ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 3108
[1]: ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 2463

Anyone have an idea what is going on?

[MarcoBoeringa]

I guess you may be running in the same configuration issues Vince pointed out in this thread.

See the reference Vince pointed out in that other thread:
http://support.esri.com/en/knowledgebase/techarticles/detail/39119

And see this 10.1 Help page for how to solve this (configuration needed on your Oracle database server):

Configuring the Oracle extproc to access the geodatabase with SQL

You essentially need to "tell" Oracle that there are additional (ESRI) libraries it can use to access the SQL functions specific for ST_Geometry. If you don't do this, you will still be able to use ST_Geometry in your database, but not all of the spatial functions like the ST_INTERSECTION you attempted to use, as Oracle will not have knowledge of what to do with these statements.

[VinceAngelo]

You really need to contact Tech Support.  Upgrading to the terminal SP wouldn't hurt either.

- V

[xuexiaolong]

Who solved the problem, seek help

[xuexiaolong]

You really need to contact Tech Support.  Upgrading to the terminal SP wouldn't hurt either.

- V

Do you know how to solve this problem

[VinceAngelo]

I'd contact Tech Support (after installing 10.1sp1 and all relevant patches).

- V

[RyanLuedey]

I was getting this same error when using ST_INTERSECTION, turns out the datasets had a different spatial reference.  Both datasets had the same coordinate system according to ArcCatalog, but when queried with ST_SRID I was getting different numbers.  ST_INTERSECTS did not have a problem with the discrepancy, but ST_INTERSECTION did.  Adding ST_TRANSFORM to get the numbers matching seems to have solved the problem.  Hope this helps somebody in the future.

[CarlosSousaFerreira]

I was getting the same error and correcting the spatial reference also worked for me (so I'm one of those "..somebody in the future"  Thanks!).

Actually, in my case, the two datasets were using the same projection system, but, as the tolerance and resolution were different, they had different 'internal' SDE SRID's.

I fixed it not through the ST_TRANSFORM but by creating a new feature class, importing the SRS from the other feature class I was intersecting it with, importing the field mapping from the original feature class and then loading the original objects into the new feature class.

[ForrestJones]

This appears to be the following issue under investigation:

NIM101461: When feature classes with different SRIDs are used in an ST_intersection query, it fails ...

The workaround is to use st_transform as rluedey mentiones.