Recently one of my customers indicated that they used the following UPDATE statement in Oracle at version 10.1 of ArcSDE to calculate acres of polygons, but that it didn't work anymore after we upgraded ArcSDE to version 10.2.2.
update SDE.ACTIVITY_TIMBERHARVEST a set gis_acres = round (to_number(((sde.st_area(sde.st_transform(a.shape, 5070)))*0.0002471054)),3);
At 10.2.2 it throws the following Oracle ERROR stack:
ORA-20603: Spatial References are not compatible
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 720
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 2623
This lead me to verify that our external procedure set up was good for Oracle. Then I checked the spatial reference of the source data which was using NAD93 datum and the Albers (5070) spatial reference which also was using NAD83 datum. Then I checked the two packages referenced in the error message ST_GEOMETRY_SHAPELIB_PKG and ST_GEOMETRY_OPERATORS to make sure they were valid and up to date.
Everything checked out. The problem had to be with the data itself.
I wrote the following PL/SQL block which uses a cursor to step through all the features in the ACTIVITY_TIMBERHARVEST feature class to see if I could find the bad features. The code uses the same update statement to calculate GIS_ACRES as the original statement the customer was using, but does it one feature at a time. When the feature is processed by ST_TRANSFORM correctly it updates the GIS_ACRES field with the acreage. When the feature is bad, it updates GIS_ACRES with 0.
Sure enough the code found 218 features out of 465261 that ST_TRANSFORM (10.2.2) could not handle. Upon closer inspection, I realized these features were "self-intersecting" polygons. At version 10.1, ST_TRANSFORM would process these types of features, at 10.2.2 it does not.
The error messages from Oracle don't indicate that the features are bad and ESRI customer support could not find the problem.
PRAGMA EXCEPTION_INIT(SR_EXCEPTION, -20603);
CURSOR c1 IS
FROM SDE.ACTIVITY_TIMBERHARVEST ORDER BY ROWNUM FOR UPDATE OF GIS_ACRES NOWAIT;
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
FETCH c1 INTO v_Rownum,v_Objectid,v_Shape,v_GisAcres;
EXIT WHEN c1%NOTFOUND;
-- DBMS_OUTPUT.PUT_LINE('Attempting to update ROWNUM:' || v_Rownum || ' OBJECTID:' || v_Objectid);
SET GIS_ACRES = ROUND (TO_NUMBER (((sde.st_area (sde.st_transform
))) * 0.0002471054)),
WHERE CURRENT OF c1;
WHEN SR_EXCEPTION THEN
DBMS_OUTPUT.PUT_LINE('Tranform ERROR on ROWNUM: ' || v_Rownum || ' OBJECTID: ' || v_Objectid || ' GIS_ACRES not updated');
UPDATE SDE.ACTIVITY_TIMBERHARVEST SET GIS_ACRES = 0 WHERE CURRENT OF c1;