how to insert a giant geometry into a field of type SDO_GEOMETRY?

Question

I am trying to insert this http://pastebin.com/cKXnCqx7 geometry into an SDO_GEOMETRY field, as follows:

declare
str clob = http://pastebin.com/cKXnCqx7
begin
INSERT INTO TEMP_TEST_GEOMETRY VALUES (SDO_CS.TRANSFORM (SDO_GEOMETRY (str, 4674), 1000205));
end;

And is generating the following error:

Please post the error as text rather than an image. The problem isn't the call to the `sdo_geometry` constructor; it's how you're trying to set the CLOB value. You would have to build it up in 32k chunks. (Like the first code [here](http://stackoverflow.com/a/17359646/266304); I'm sure there are better examples). But where is that string coming from? Can you read it from a file, for instance? — Alex Poole, Nov 08 '16 at 21:09
I can see it's a string. It's a string that is more than 32 characters long, which means you can't assign that directly to a variable - the maximum length of a string literal is 32k in PL/SQL. You aren't typing that into your code. Are you copying and pasting from a file? If so can that file be put somewhere you could read it with `utl_file`? Otherwise you'll have to manually chop your string up in to smaller chunks to create the full CLOB value. — Alex Poole, Nov 09 '16 at 10:45
This string comes from a json, I understand what you're talking about — Max Ferreira, Nov 09 '16 at 10:52

score 4 · Accepted Answer · edited May 23 '17 at 12:24

The maximum size of a string literal is 32K in PL/SQL. You're trying to assign a 98K+ literal, which is what is causing the error (although since that refers to 4k, that seems to be from a plain SQL call rather than the PL/SQL block you showed). It isn't getting as far as trying to create the sdo_geometry object from the CLOB.

Ideally you'd load the value from a file or some other mechanism that presents you with the complete CLOB. If you have to treat it as a literal you'd have to manually split it up into chunks; at least 4 with this value if you make them large:

declare
  str clob;
begin
  dbms_lob.createtemporary(str, false);
  dbms_lob.append(str, 'POLYGON ((-47.674240062208945 -2.8066454423517624, -47.674313162 -2.8066509996, <...snip...>');
  dbms_lob.append(str, '47.6753521374 -2.8067875453, -47.6752566506 -2.8067787567, -47.6752552377 -2.8067785117, <...snip...>');
  dbms_lob.append(str, '-47.658134547 -2.8044846153, -47.6581360233 -2.8044849964, -47.6581811229 -2.8044926289, <...snip...>');
  dbms_lob.append(str, '-47.6717079633 -2.8057792966, -47.6717079859 -2.80577931, -47.6717083252 -2.8057795101, -47.6718125136 -2.8058408619, -47.6719547186 -2.8059291721, -47.6719573483 -2.8059307844, -47.6719575243 -2.8059308908, -47.6719601722 -2.8059324729, -47.6720975574 -2.8060134925, -47.6721015308 -2.8060157903, -47.6721017969 -2.8060159412, -47.6721058088 -2.806018171, -47.6721847946 -2.8060611947, -47.6721897923 -2.8060650985, -47.6722059263 -2.8060767675, -47.6722070291 -2.8060775047, -47.6722416572 -2.8060971165, -47.6722428566 -2.8060976832, -47.6722611616 -2.8061055189, -47.6722666301 -2.8061076243, -47.6722849174 -2.806116847, -47.6722862515 -2.8061174528, -47.6723066339 -2.8061257231, -47.6723316499 -2.8061347029, -47.6723426416 -2.8061383836, -47.6723433793 -2.8061386131, -47.672354519 -2.8061418177, -47.6723803034 -2.8061486384, -47.6725084039 -2.8061908942, -47.6725130545 -2.8061923817, -47.6725133654 -2.806192478, -47.6725180423 -2.806193881, -47.6728423039 -2.8062879629, -47.6728698649 -2.8062995965, -47.6728952856 -2.8063088527, -47.672897007 -2.8063093833, -47.672949984 -2.8063200428, -47.6729517767 -2.8063202193, -47.672966443 -2.8063209226, -47.6729679855 -2.8063213223, -47.6733393514 -2.8064196858, -47.6733738728 -2.8064264543, -47.6733761939 -2.8064267537, -47.6733804796 -2.8064270239, -47.6733890639 -2.806431641, -47.6734057692 -2.8064398944, -47.6734069006 -2.8064404055, -47.6734241363 -2.8064474849, -47.6736663052 -2.8065373005, -47.6736676833 -2.8065378073, -47.6736677752 -2.8065378409, -47.673669156 -2.8065383402, -47.6737754465 -2.8065764489, -47.673793217 -2.8065850801, -47.6737945765 -2.8065856723, -47.6738366895 -2.8066000123, -47.6738381279 -2.8066003728, -47.6738811819 -2.8066074503, -47.6739137725 -2.8066153813, -47.6739159177 -2.806615636, -47.6739318345 -2.8066165588, -47.673951326 -2.806622013, -47.6739530661 -2.8066223195, -47.6739793945 -2.8066256302, -47.6740948445 -2.8066344025, -47.674240062208945 -2.8066454423517624))');
  INSERT INTO TEMP_TEST_GEOMETRY
  VALUES (SDO_CS.TRANSFORM (SDO_GEOMETRY (str, 4674), 1000205));
  dbms_lob.freetemporary(str);
end;
/

You can make the chunks smaller and have many more appends, which may be more manageable in some respects, but is more work to set up. It's still painful to do manually though, so this is probably a last resort if you can't get the value some other way.

If the string is coming from an an HTTP call you can read the response in chunks and convert it into a CLOB as you read it in much the same way, using dbms_lob.append. Exactly how depends on the mechanism you're using to currently get the response. Also worth noting that Oracle 12c has built-in JSON handling; in earlier versions you may be able to use the third-party PL/JSON module, which seems to handle CLOBs.

how to insert a giant geometry into a field of type SDO_GEOMETRY?

1 Answers1