Home » Server Options » Spatial » ORA-13034: Invalid data in the SDO_ORDINATE_ARRAY in SDO_GEOMETRY object (split from "ORA-0939: too many arguments for function")
ORA-13034: Invalid data in the SDO_ORDINATE_ARRAY in SDO_GEOMETRY object (split from "ORA-0939: too many arguments for function") [message #684784] Fri, 20 August 2021 16:18 Go to next message
gchiluveru
Messages: 1
Registered: August 2021
Junior Member
Hi Frank,

I tried to follow this but at run time getting below errors.

Connecting to the database 135.13.19.112_ads_user1_sc.
ORA-20001: ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
ORA-06512: at "ADS_USER1_SC.TESTGANFUNCTION1", line 33
ORA-06512: at line 5
EXCEPTION WHILE SAVING TO ADS
ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
ORA-13034: Invalid data in the SDO_ORDINATE_ARRAY in SDO_GEOMETRY object
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 970
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 482
-29875



, pasting my function code below per reference.


CREATE OR REPLACE FUNCTION TESTGANFUNCTION1 RETURN VARCHAR2 AS
BEGIN

DECLARE
ord SDO_ORDINATE_ARRAY := sdo_ordinate_array (-121.96385622407949, 37.76571499562515, -121.9351887745855, 37.81373790965025,-121.86377764291186, 37.79800502553437, -121.90248728279747, 37.77043049076258, -121.96385622407949, 37.76571499562515);

BEGIN

FOR i IN 1 .. 5000 LOOP
ord.EXTEND;
ord(i) := i; -- Initialize a large ordinate array...

END LOOP;

delete from ADS_POLYGON_SHAPE where county_key = 1911;

insert into ADS_POLYGON_SHAPE (county_key, polygon_shape) values (
1911,
SDO_GEOMETRY(
3,
8307,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1),
ord
)
);

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION WHILE SAVING TO ADS');
DBMS_OUTPUT.PUT_LINE (SQLERRM);
DBMS_OUTPUT.PUT_LINE (SQLCODE);
ROLLBACK;
raise_application_error(-20001,SQLERRM);
--RETURN NULL;
END;
COMMIT;
RETURN 'SUCCESS';

END TESTGANFUNCTION1;





Thanks
Gan
Re: ORA-13034: Invalid data in the SDO_ORDINATE_ARRAY in SDO_GEOMETRY object (split from "ORA-0939: too many arguments for function") [message #684787 is a reply to message #684784] Sat, 21 August 2021 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

I think the error message is clear:
ORA-13034: Invalid data in the SDO_ORDINATE_ARRAY in SDO_GEOMETRY object
 *Cause: There is invalid data in the SDO_ORDINATE_ARRAY field of the
         SDO_GEOMETRY object. The coordinates in this field do not make up a
         valid geometry. There may be NULL values for X or Y or both.
 *Action: Verify that the geometries have valid data.
There are many errors in your code:
  • "EXCEPTION WHEN OTHERS THEN", this is a big error, read WHEN OTHERS.
  • "ROLLBACK" in the exception block of the procedure is useless as if the procedure fails, Oracle will rollback its changes (unless the caller ignored the exceptions with an erroneous WHEN OTHERS.)
  • "raise_application_error(-20001,SQLERRM);", no, just "RAISE;"
  • "COMMIT" should never be in a procedure, the caller knows if it wants to commit or rollback the whole work, the procedure doesn't and can't know the whole process it is part of.

[Updated on: Sat, 21 August 2021 01:21]

Report message to a moderator

Re: ORA-13034: Invalid data in the SDO_ORDINATE_ARRAY in SDO_GEOMETRY object (split from "ORA-0939: too many arguments for function") [message #684807 is a reply to message #684787] Wed, 25 August 2021 10:02 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
Furthermore to @Michels post, the spatial part of your query is incorrect.
You define a spatial geometry with sdo_gtype=3, which is invalid. Compare Valid SDO_GTYPE Values.

If you use the correct sdo_gtype, there is another error in your geometry ORA-13367: wrong orientation for interior/exterior rings

SELECT SDO_GEOM.VALIDATE_GEOMETRY(shape,0.01) val
  FROM
 (SELECT sdo_geometry(2003,8307, NULL
       , sdo_elem_info_array(1,1003,1) 
       , sdo_ordinate_array (-121.96385622407949, 37.76571499562515, -121.9351887745855, 37.81373790965025,-121.86377764291186, 37.79800502553437, -121.90248728279747, 37.77043049076258, -121.96385622407949, 37.76571499562515)) shape
    FROM dual);

val
-----
13367

RECTIFY_GEOMETRY can solve the problem and reverse the geometry.

SELECT SDO_GEOM.VALIDATE_GEOMETRY(sdo_util.rectify_geometry(shape, 0.01),0.01) val
  FROM
 (SELECT sdo_geometry(2003,8307, NULL
       , sdo_elem_info_array(1,1003,1) 
       , sdo_ordinate_array (-121.96385622407949, 37.76571499562515, -121.9351887745855, 37.81373790965025,-121.86377764291186, 37.79800502553437, -121.90248728279747, 37.77043049076258, -121.96385622407949, 37.76571499562515)) shape
    FROM dual);
val
-----
TRUE

[Updated on: Wed, 25 August 2021 10:04]

Report message to a moderator

Previous Topic: SDO_UTIL.REMOVE_DUPLICATE_VERTICES doesn't remove duplicate vertices
Goto Forum:
  


Current Time: Thu Mar 28 15:04:40 CDT 2024