Search code examples
sqloracleoracle-spatial

Max size of SDO_ORDINATE_ARRAY / Oracle Spatial

I'm querying an Oracle Spatial database with this query (I left out the other 1496 coordinates):

SELECT *
FROM   pointsofinterest
WHERE  Sdo_inside (pointsofinterest.geoloc,
              Sdo_geometry(2003, 4326, NULL, Sdo_elem_info_array(1, 1003, 1),
              Sdo_ordinate_array(4.237378120400001, 43.7904510498, 4.2357025146,
              43.7882575989
                                  ,
                                            4.232352256800001, 43.7882575989,
              4.232352256800001, 43.7871589661))) = 'TRUE'

and get this error:

ORA-00939: too many arguments for function 00939. 00000 - "too many arguments for function" *Cause:
*Action: Error at Line: 4 Column: 27

The SDO_GEOMETRY object contains 1500 2D-coordinates. That should not be a problem according to http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_objrelschema.htm#SPATL489

When I remove a lot of coordinates, there is no error.

Am I missing something about the max number of coordinates in the SDO_GEOMETRY constructor? Why am I getting this error?

Solution

  • This error comes from one of the limitations in SQL: the maximum number of arguments that can be passed to a stored function or procedure, i.e. 999 arguments (actually 1000 including the return value). This is because sdo_ordinate_array() is actually a function - the constructor of the sdo_ordinate_array type.

    The proper approach (which is also more efficient) is to pass the sdo_geometry object as a bind variable and use that in the query. The way that is done depends on the language you use to submit the select statement ...