topic Re: error using st_point with stored procedure (works in sqldeveloper but not in java) in Developers Questions

error using st_point with stored procedure (works in sqldeveloper but not in java)

davidzornosa — Wed, 01 Apr 2015 00:32:55 GMT

A simple procedure:

create or replace PACKAGE BODY TEST_PACK AS

procedure test as

begin

insert into table_evw(shape) values (sde.ST_GeomFromText('point ('|| -73.16 || ' ' || 6.78 || ')', 4686));

end test;

END TEST_PACK;

ran it in sqldeveloper:

begin

TEST_PACK.test;

end;

runs fine, i get a brand new record on 'table_evw'.

But when i invoke it from java

CallableStatement cs = c.prepareCall("{call test_pack.test}");

cs.executeQuery();

I get this horrible exception

ORA-20004: Error generating shape from text: Shape has too many parts (-5).

I've already tried

alter instance set NLS_NUMERIC_CHARACTERS = ".,";

And it doesnt work

Oracle 11g.

ArcGIS 10.2

Thanks.

Re: error using st_point with stored procedure (works in sqldeveloper but not in java)

CarlosNantes — Thu, 25 Feb 2021 19:43:45 GMT

This is still a question in 2021. I had the same issue today when using SDE.ST_POINT(). Turns out that the problem is related to NLS_NUMERIC_CHARACTERS config.

SDE.ST_POINT will throw "Shape has too many parts" if NLS_NUMERIC_CHARACTERS =",.". This is probably the case for sde.ST_GeomFromText().

To solve the issue, use dbms_session.set_nls() in the first line of the stored procedure or function as follows:

create or replace PACKAGE BODY TEST_PACK AS

procedure test as

begin
dbms_session.set_nls('NLS_NUMERIC_CHARACTERS', '".,"');

insert into table_evw(shape) values (sde.ST_GeomFromText('point ('|| -73.16 || ' ' || 6.78 || ')', 4686));

end test;

Solution found here: StackOverflow