EXTPROC returns errors

3843
1
02-10-2016 09:55 AM
SherrieKubis
Occasional Contributor

Oracle RDBMS 12.1.0.5

Oracle Enterprise Linux 6.6

SDE 10.2.2 Patch 290200

We use ST_GEOEMTRY and ST functions, mostly ST_INTERSECTS.  EXTPROC is configured and works well -- most of the time.  We have this weird problem, when for no reason that I can find (ANYWHERE!), a feature class that participates in an overlay function, returns an error.  They are always reference layers, meaning they are static and don't change.  To avoid a production issue, I've created a health check program that creates a dynamic SQL that does an ST_INTERSECT with two layers; I then interrogate the log file and send an email when an error happens.  The error:

ORA-28579: network error during callback from external procedure agent

ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 100

ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 340

The fix is to recreate the feature class from ArcCatalog, and then insert rows from the bad feature class, and generate statistics.  I've asked the SDE Administrators to keep the erroring feature class so that I can use it for testing, looking for differences. 

Last night the health check ran without an issue.

Today, I have two feature classes to work with:  COUNTIES and COUNTIES_OLD.   I know that COUNTIES is the current, valid FC, and COUNTIES_OLD was one that returned an error on 1/11/2016. 

My first test showed that the reverse is true.  While COUNTIES was fine at 3am, it now returns an error.  While COUNTIES_OLD returned an error on 1/11/2016 it returns fine today. 

Oddly enough, I see two different errors returning from COUNTIES, depending on an embedded literal:

select ' fc: COUNTIES' , a.owner, a.numwells

from asr a left outer join COUNTIES c

ON sde.st_intersects (c.shape, a.shape) = 1;

select ' fc: COUNTIES' , a.owner, a.numwells

*

ERROR at line 1:

ORA-20011: Error generating shape. (SHAPE1)

ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 100

ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 340

Removing the literal, with the same SQL (and this is the error I usually get, even with the literal):

select a.owner, a.numwells

from asr a left outer join COUNTIES c

ON sde.st_intersects (c.shape, a.shape) = 1;

select a.owner, a.numwells

*

ERROR at line 1:

ORA-28579: network error during callback from external procedure agent

ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 100

ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 340

This was odd enough behavior, not this makes it weirder.  We had an open ticket with ESRI support, who asked for our data.  After much back and forth, the answer was 'something in your data - recreate'.  I also have an open SR with Oracle support.  They've been very thorough and helpful, looking at traces, statistics, but their ultimate answer was to go back to the vendor. 

Maybe someone has an idea or has experienced similar issues.  Any insights are appreciates. 

0 Kudos
1 Reply
SherrieKubis
Occasional Contributor

Before too long I'm proving myself wrong--somewhat.

The literal doesn't have anything to do with the different errors.  The first execution returns

ORA-20011: Error generating shape. (SHAPE1)

ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 100

ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 340

In the same session the next error is:

ORA-28579: network error during callback from external procedure agent

ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 100

ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 340

Sometimes we see just the second error.  My mistake earlier is that I was testing scenario one, then scenario two immediately. 

It doesn't change the problem much, in the for an unknown reason, this error pops up. 

0 Kudos