ST_AsText returns "ORA-20006: Error generating text from shape (-1)"

[VargeseAntony]

Hi,

ArcSDE version:- 9.3 SP1 32-bit
Oracle Version: - Oracle 10.2.0.4

We have a line shape with 24142 points, when we execute the query �??Select LENGTH(SDE.ST_AsText(SHAPE)) from <table name> where id=<id>�?� the below error occurs

[Error] Execution (1: 1): ORA-20006: Error generating text from shape (-1)
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 70
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 68

But with ArcSDE 10 SP1 64-bit, the query returns the result without any error.

-Is there any restriction for ST_AsText() in the 9.3 version?
-Is this a known issue, and do we have any fix for this?

Thanks.

Regards,
Vargese.

[VinceAngelo]

You need to configure the listener to use the EXTPROC library for shape support.
You seem to have done this with 10.0, but not 9.3.

To answer your questions:

-Is there any restriction for ST_AsText() in the 9.3 version?

No

-Is this a known issue, and do we have any fix for this?

It's a configuration error, which is applicable to all releases.  A google on
the error message locates this KB document.

It is curious that you would ask about fixes when there are two years of
patches which have not been applied to your system (the terminal patch
set for 10.0 was SP5).  10.0 is four months from "Mature" support status,
so it's unlikely there wil be any more patches. 9.3 has been in Mature
status for over a year, and will be retired in December.

- V

[VargeseAntony]

Thanks for the reply.

If EXTPROC is not configured, it should give me error like "PL/SQL Error loading External library", again the same error should occur for every shape, I have other shapes for which the query executes successfully.

Regards,
Vargese.

[VinceAngelo]

9.3 SP1 was released in March 2009.  There were five upgrades of the geometry
libraries between then and 10.0sp1 (and twelve leading to 10.2). Upgrading for
each snapshot from 9.3.1(final) to 10.0sp1 until the geometry works is probably
the easiest way to figure out where the behavior of that particular shape changed,
though Tech Support might be able to pinpoint it with effort. 

Your best action would probably be to stand up an 9.3.1sp2 instance and to see
if the library fix occured before the architecture change at 10.0.

- V

[VargeseAntony]

Retrieving shape with large number of points is an issue in 9.3 version.

After upgrading to 9.3.1, the issue is not seen anymore.

Thanks.

Vargese