I have a query to insert 6 geometry data like this

DECLARE v_geom sdo_geometry; 
  v_geom := MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(107.3151187889,-7.32918547322475,107.315103530116,-7.32918547322475,107.315092087376,-7.32918165900357)); 

That query does not error when executed. but when I insert around 60.000 of geometry data, an error appears like 'Pl/SQL ignored ......'

So how can I insert 60.000 of geometry data to Oracle ?

  • 2
    What is your use case for needing 60k geometries in a single row? If you Edit the question to state your goal, instead of this solution, you might get answers that will work. As it stands, this appears to be an XY Problem – Vince Dec 21 '18 at 13:37
  • 1
    Ahh. Now on a Desktop device, it appears you mean 30k vertices (X,Y pairs) in a single geometry. This should be easy enough, though you might be running into buffer overflow by assigning a variable first. This seems more a question for Database Administrators. – Vince Dec 21 '18 at 14:41
  • Oracle has a default max value for number of coordinates. Increasing it may require a creation of database from the scratch. – user30184 Dec 21 '18 at 16:10
  • The Oracle ordinate maximum is 1,048,576 (524,288 2-D points, 349,525 3-D points,...) so this is not an ordinate maximum issue. Please Edit the question body to add the exact error to the question. – Vince Dec 21 '18 at 17:09
  • This is a really weird use case. You mean you actually have a PL/SQL statement with 60,000 hard coded points (i.e. 120,000 numbers) ? Where did those coordinates come from ? Surely you read them from somewhere ... In which case you should have some logic to construct the geometry array from those coordinates. Or if you get your data from some GIS file format, load directly from that file. The process of inserting massively complex geometries the way you do is massively inefficient. It works (I tried with 120,000 points) but is horribly slow. – Albert Godfrind Dec 21 '18 at 17:16

I think you are saying you are not able to store your insert as a textual plsql block.

While there is not a production use case for writing a geometry insert for a geometry with 60,000 vertices as plsql or sql text, sometimes it is helpful to be able to pass about a problem or example as a text statement. The issue is that for Oracle when you type out the raw SDO_GEOMETRY constructor each individual ordinate is seen by the parser as a distinct DIANA node. So you just quickly run out of nodes and get the error message that the program is too large.

"One simple trick" for writing modest-sized geometries would be to store the insert statement as dynamic SQL text. In this case the text sql string is seen by the parser as a single node and thus you can cram in 32,767 characters worth of geometry constructor text. But that won't work for your 60,000 ordinate geometry.

For big stuff I have written the following utility which will crunch your geometry into a compressed hex string for placement inside a plsql statement (and decompress it afterwards) https://github.com/pauldzy/DZ_SDOTXT This should allow you to store your 60,000 vertice geometry as a plsql insert statement for sharing with others. There are still limits using this technique but they are much, much higher.

Here is an example of the code in action whereby folks were arguing about an Oracle Spatial problem caused by a geometry which happened to be too large to provide as straight-forward text in a plsql block within a forum entry. So I just packed it down so we could all see and test what was happening. https://community.oracle.com/message/14235952#14235952

Hope that is helpful. Note again this should be only used to share geometries between humans and not as part of your production ETL processes.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.