Oracle throwing "too many arguments for function" when using Oracle Spatial DB (MDSYS.SDO_GEOMETRY)

Question

I'm trying to insert some polygons using MDSYS.SDO_GEOMETRY, but I'm getting the error "too many arguments for function", I did a simple test, because I suspected there was a limit of information for the insert.

The test was as follows, I inserted a very simple polygon, like a rectangle, into the base, the first one was inserted successfully, it was this little guy here:

    -47.907596018183, -15.823197195244,
    -47.910025764977, -15.822772155044,
    -47.910292291122, -15.827300809061,
    -47.908579370537, -15.827056272436,
    -47.908374813318, -15.823197247034,
    -47.910025764977, -15.822772155044,
    -47.907596018183, -15.823197195244

. So then I inserted the same polygon several times, like overwriting it, inserting it over and over again, thus increasing the size of the insert:

I arrived at line 891 of the insert (hehe, I was crazy enough to do this test lol) I had the problem of "too many arguments for function", when I removed a coordinate, leaving 881 it inserts successfully, so there really is a limit to the size of the insert.

Does anyone have any idea how I can solve this problem? The polygon that I must insert reaches line 4000, it is huge.

Answer 1

There is a document in MyOracleSupport (Doc ID 160012.1) describing the limitation. You can overcome the limitation, for example by inserting the geometries in a PL/SQL block rather than plain SQL.

DECLARE
MY_GEOM MDSYS.SDO_GEOMETRY;
BEGIN
MY_GEOM := MDSYS.SDO_GEOMETRY (2, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY (1,2,1),
MDSYS.SDO_ORDINATE_ARRAY (
1, 2, 3, ..... 1997, 1998, 1999, 2000)
));
INSERT INTO GEO_TABLE (GEOM) VALUES (MY_GEOM);
END;
/