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

davidzornosa

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.

CarlosNantes

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