DZ_SDOTXT

Utilities for the conversion and inspection of Oracle Spatial objects as text. For the most up-to-date documentation see the auto-build dz_sdotxt_deploy.pdf.

Generally there are few reasons for you to want to manifest Oracle Spatial objects as SQL text. So you should only be using this code if you need to generate an example for an OTN posting or a very simple Oracle SR, or if you are exchanging a very modest amount of data with a colleague who has limited access to Oracle. Overwhelmingly the proper way to exchange Oracle data is via datapump.

See the DZ_TESTDATA project as an example of what this repository can do.

Manifesting large Oracle Spatial geometries as sql text

In the paragraph above I state that you really should never attempt to dump anything beyond small sample geometries to text. Unlike other databases (such as PostgreSQL) you are greatly limited as to how much information you can persist in a given block of PLSQL code. When you submit a geometry in textual form via the object constructor, each ordinate is viewed as a node by the code parser. So again, don't go this route. But you are still reading! Yes, okay sure, sometimes one may really want to get a large geometry into a chunk of text to share with a colleague or post as a gist or such. But of course never do this in your production or really any workflow. With that out of the way I provide code to allow you to crunch a geometry into a lob which can be dumped to text as hex and then afterwards reconstructed. There are still limits but this should allow you to persist as text geometries that are much larger than what you can persist in the object constructor form.

Given a larger geometry

SELECT
dz_sdotxt_main.blob2plsql(
    dz_sdotxt_main.sdo2geomblob(a.shape)
) AS lob_text
FROM
foo a
WHERE
a.objectid = 44;

this will create a set of lob append statements that you can paste into a chunk of PLSQL. The size of each hex dump line is limited to allow it flow through SQLPLUS. Now each of these statements will utilize a couple of nodes in the code parser but far less than one node per ordinate, also the blob is compressed using UTL_COMPRESS.

DECLARE
   dz_lob BLOB
   dz_sdo MDSYS.SDO_GEOMETRY
BEGIN
   ---- START: This is the portion provided by sdo2geomblob
   DBMS_LOB.CREATETEMPORARY(dz_lob,TRUE);
   DBMS_LOB.APPEND(dz_lob,HEXTORAW('1F8B08000000000002038CBD59D2E5BAAE343622EF60DFCCE10FBF793667F0662201105A9F54F67D38716B57961645B14193489494EAFFFED76A19FFFBDFFFFDFFFC9FFF13FEE77FF97F197F9BFFF7BFFF2BE7F45F1965E7B1CFFF1D704BFFEDDD77CEE78FB5D76C903C6B4929654794857F506753441FA301517A21641CF481A474FE8342DA6EE7BFA69AAB410A7EB7F4551D525A07647683D40359EB3CCA2169CFF3AFD66E0E59F8632E36DA9E7BEF0732CA32C8EC7C8A43D24C1B2FD4FD8716DEA8E53B96D4F20464B56F48D90D33D78743F0C7369ACD4BDBBD55BCD1F01F6AC0D433A10E39937620AD657FA37A863BD6996E858CBD64FE4BFDFA44ED4C2B867B1EAF9099E4A5CF7B1824E5D9E42BEA0FF5DE30BB3377FBA13A77C21B9D3755482B98CA566B75C8DA78A352FB2FA45C485F322DF31F9086CFB88723B016CE7FB3D1D671A60AA39DC520F8D03B577FE73ACEC739FF69369B96D60AA6254F7FA1F39F862C97E510FC71367F086612BF53FD7776C13F384B512165F7BA30B7C91ED2934C525AD320E7073266DF1677EF191F118371489737DEB93984DBEE8C4D21B3E622B362D3D26BC20F4DDF6865CC3EE587A643F0D8B33C6C6E4B5D035F28DFB17479A35D1C729609F6622AFE94990119CBC6924AEAEDF19DC7C4D49D7F6B6F7456D29493C3E79F7FAE58A702C967361386379ABDD194373C074A36083E90FCD0764897A7E4EE90BAF0D2E53E450EA0F38ACD20E7FBC9E952ED8D640D9E7FB68641524DB2A37D1325F944CB766B3EFF7F956949B616D6C20BD950D76AF2BB6BDB9CAC86695B67FB1964EE219FB5FB23E48B9EB3C4DEE66CC9CEAD6A883965BFDB5179966F95DD31D63D2AF19434EB36486D5CC9C321B3C853D2BA90BC64A9AC4F481E958B707E9C60799CD7E16AD2CF33B2FC703D3BD320E7F360F19F311A6414B91F6CC19D9F398B05103B29B31CC9FB22CED72A558E413B6F4B16C89C36B77D763957CEDF18A4C8662EB539E4BC8FAC141B4A911D74D6A54DFFB9AAE46AEAD37F487650DA76831CC839AFB1981CC173E56CD08B28EDF93B7F20AD2F99DB65D37F16A8DC77F63E0D3304801D3DE763606A650E1432E5423C37A2FD4EADB297675B0EA94B302B7F43F80D8BDD1F07223F34C6BE902A2BDFCE9503C12EC421E28882F19FEBE917911D722E67FC8EED8F57885CA36E6DFCFD9DF33D320F4A4360C1AD739DDB276CBD0C5908C307DBAB4C9C2F84964AC695597D565A92A79CFF4721F51CD95CFCCB21D888EB6C3E839C3FE30B4DB37C86DC53C794C8F642153387B174FF2159A4E710B6B92D9DD6C6198C42BA980D69757B4A693C69B6EFD531E4E44F767A95B387B63C43A77F9E2D27FBD0AE667C9E8C7D981D30B8DDFD19670A64F2CD783A1059'));
   
   ...... LOTS MORE POSSIBILY .....
   
   DBMS_LOB.APPEND(dz_lob,HEXTORAW('A0D98EFD7CAE5B39C357DF06E152394B4821E740E189307552CEFEEBB2DD6D523072F986C3AEDDF3D4241FA82F87B48A655BFC7DCEED2713D0FC29E7E26B32B58EC046ADC316423AD324FBE3FC8541B60CE5BCA643120C21FC6787C80C2C9BB7B47BA219E7A3ADB210CE19AE0BE19C8A5356DC9A0E113B4EAC3685D4736A62ADD8E57D5E07C33D0F2F0639C7DB9033CEDEF918BCF24376A71E4BF598B25B4E598374F98A650C839C652AA7D3F6A74CB106B3CDEDF9E522B6462E8E90D369F868C71A6DC9AD6B2F2406CB6EC72834C8996DBC505F0E29B446CCEC4967D1D3A4F469817D7E5E28657FCAF9192CCAB3801C426BA4DA0B1D6B563EEC32B3FF4CC2A465649FA8D72257CA34136CCA2D7BBC806E633967C2C01B6C7FCA3948C460F4C96D6773E670BDCFD164556633F50EA289337406E010F16346BA90224680DB710722264DF22F748C2B590A6EDE1E08FE9CB7EDB30369134765B523F940C41BDA3E58AC16CC6DBA081E2C66459FC5C489EA76C8CD218B65DD857BB6C76AB2586CF98F213BE4D891063927B0AC16DF4462E99DCF6CA776AAF2CD70B6D9F44F4CD3B9B27D5ACE66161BED5C890E914D347C5ACE954997AA3B446E2A7C6E839CF320CB267288CCFF3166EC29E721725F63FF2A44DEA8FA3977EE399A31CB1CB33971B62C3F378E9996C45A6CF6CA537EB6F84178AED3DE87D83D36928587E2CBDA60CFDADED888CD6CCEF310D9CECD2EB373F4649AC8D54792E4F439E7AA418E4D2957EFF6755BE51A3A4BDC20B09D30FDBE871A2C9F058FC220C7B4E51EF2D347DC8B7C6C53878C2A3FEE4BAEF21ADAE63AE05D92389A7EFAD72613977DFAB164C592EBF729598CB07C7FE84CA3FCAFCD0B1C5A9C611B3F94F67FB2D86429940B918FC8F39490D1C4204917D278B61B825BE81CA7D3CF4AB1B09A9C1B802C4C9D5CE0DDBF62929DD80D32E164C94564932BCEF53986477648D9322DE94278121AA04C31D8A719AFC78FA6832E270B200346BD7C435F4F0BF6EDB195B7417ACEEB71C82D593C99A7A9408E2D21C786050BE61AE2E620D4639024475878CA1FC8B119E487BE007D8B570643FE1B32E913FB1DF317B2C6A4BDFE0D81F72A56CD1DACACE2E973CB000A36A23F65263A5DCB21459E927CFAFF42CE2FC805E2FB6389C70BA04364A99CBF299F90B39A64CF587CE440B06C53F7E57456A0F80E79DCB14CB92D66BF902A5F68D66F48E653FAFE1A4B9B4B56FBB8F3D2C59F6DA93924CBA5BEB2FF50930F901D90F811EFC4FD00CE91B3A2573617E327DD37C7B14F647B6FB724569753708DEE9063DBC957F65D28374EBEDFA79DA995A1DC852D66740E2F7CAC1A1EDA77516230ED4E5B958057F044CF269B1205D836DC8A5912C3C79F2226639A735F88DCF0D32D'));

   ---- DONE
   
   dz_sdo := dz_sdotxt_main.geomblob2sdo(dz_lob);
   
   -- Now do something with the geometry
   
END;
/

Again I am not entirely sure of the upper limit of this technique in terms of the number of ordinates. Drop me a line if you sort it out. The above solution uses the secret MDSYS.SDO_UTIL.TO_CLOB and MDSYS.SDO_UTIL.FROM_CLOB functions to dump and reconstruct the geometry object. This should work for all forms and types of geometries as it just a manifestation of the VARRAYs as bar-delimited text. But the recipiant of the sql text dump will either need geomblob2sdo or the logic of that function to reconstruct the geometry. Take a look at the function, it's not that complicated and could be easily put into the declaration of your PLSQL block. However it is a small complication.

Another approach is if your geometry is compliant with the OGC Simple Features specification 1.0 - e.g. simple 2D straightline geometries, you can use WKB as the blob format to pass around. Note that WKB is not compressed as far as I can tell so it may be larger than the sdo2geomblob solution. But the reconstruction step is much simpler.

SELECT
dz_sdotxt_main.blob2plsql(
    MDSYS.SDO_UTIL.TO_WKBGEOMETRY(a.shape)
) AS lob_text
FROM
foo a
WHERE
a.objectid = 44;

Then to reconstruct things just pass the blob into the geometry constructor

dz_sdo := MDSYS.SDO_GEOMETRY(dz_lob,4269);

Just make sure to pass along the correct SRID to the constructor as WKB does not persist that information. Again this approach will work for the usual "run of the mill" geometries but is not as robust as the sdo2geomblob solution.

Fast dumping of Oracle record sets with geometry to sql text

As far as it goes the engine I provide here is pretty robust and will reify your individual SDO objects into text. However oftentimes you might just want to dump a table to text without manually crafting your own export routine. To that end I provide the DZ_SDOTXT_DUMPER type to automate the creation of row-by-row table inserts. For the initial version it only handles strings, numbers, dates, geometries and blobs. No doubt you have other columns types you'd like to handle in which case please add them via a pull or submit an issue.

The dumper type creates the text of the record insert as either a SQL insert or an anonymous PLSQL block insert action. The difference is all related to restrictions in the Oracle SQL engine as to how much information you can pass. If your geometries are small in size the SQL insert will work just fine. However if your resulting hex dump makes your insert statement longer than 2499 characters then sqlplus will fail. If you are using some other Oracle client and the insert text is longer than 4000 characters then that insert will fail with ORA-01489.

But if you do have some small potato geometries, you might be able to get by with just SQL:

SELECT
dz_sdotxt_dumper(
   'SELECT * FROM foo'
).sql_inserts('foo')
FROM
dual;

Example results:

INSERT INTO foo
(OBJECTID,FEATUREID,SOURCEFC,GRIDCODE,AREASQKM,WBD_HUC12,WBD_HUC12_PERCENT,SHAPE,SE_ANNO_CAD_DATA)
VALUES
(76,14784023,'NHDFlowline',1126643,.00540000422858921,'071200040101',1,
dz_sdotxt_main.geomblob2sdo(HEXTORAW('1F8B08000000000002038DCEBB0DC3400C03D08962E84789DAC148976D347C1CF80E7099460D1F489988CFD01233EFCF793ECEE8E82FD599177948476B363CE13D61075283ADCCAAC22680A4B85AD926992485E59B04D81922ED3731CBB69072E722D06BA9A281D562AE4EA76AE82646778154730DE14A89C8CA7FDE9D2F20EFA325F9000000'))
,EMPTY_BLOB());
INSERT INTO foo
(OBJECTID,FEATUREID,SOURCEFC,GRIDCODE,AREASQKM,WBD_HUC12,WBD_HUC12_PERCENT,SHAPE,SE_ANNO_CAD_DATA)
VALUES
(141,14768816,'NHDFlowline',1128916,.00269999962230127,'071200061005',1,
dz_sdotxt_main.geomblob2sdo(HEXTORAW('1F8B08000000000002038DCE3B0E02410C03D013B18A9D9FE70E888EDBE4F02C680751D2A4889FA3D0CC67C4CA99C7F37EFF1983C13BC5CC4D3A101D7473324E1C3C12284630416DE18B7DAECDFB125A509B9DD145140BE680675D64A51CDE2C6C92844753D8576A994AF2159B2093417E41A9F969FDF1EBBC00E76507F0F5000000'))
,EMPTY_BLOB());

More than likely that ain't going to work as some of your geometries are going to overtop the SQL limits.

In that case using PLSQL can push us past these limits:

SELECT
dz_sdotxt_dumper(
   'SELECT * FROM foo'
).plsql_inserts('foo')
FROM
dual;

The result will be a single clob with each row insert bundled up as an anonymous plsql block. There are still limits here as to how much data sqlplus can ingest not to mention marshalling your entire query in memory as a text clob has its own limits. So this is by no means a production ready process nor should it ever be. But it just might do the trick for you right here and right now to move some geometry around.

Example results:

DECLARE
dz_lob1 BLOB;
dz_lob2 BLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(dz_lob1,TRUE);
DBMS_LOB.APPEND(dz_lob1,HEXTORAW('1F8B08000000000002037D985B921D47084457644741413DF6E0F09F77338BF7C93B05D75F96140AA927BB1E902449FB18F3E7E7F8CA9F9FBFFFF9EBAFFFFCF5633FA69FDACFCF1F67FF7923F9B376DCF4F913FE678EB17C66EC39EF2E48EEBBEEB4D8BB20639C6973851764D9D867CD71EC41E69C31F79EE73C48A4F989330E4F7E219CCF39A4ED559038B673C4B482E4F4B593950A3287ED33478C7D1F248227DBB28FEBF7ACCDC21975A33DAEED7B4ED62ABE7C6CEE18B5C83A2B32AEADDB88B493C676B548D82534FCD5103F79364F62D59DEF9A734D7ED01022B9561089DA68CDED774487C512F8482E1A05B1E37B72A30AAE7D0EB609793E88AEA26539DC8398E7364279BC20CBEFE4ADACE30EFEB9673ADB17240661481BD910E735CEBFDE71CDF4D69D9CF91732EFBD6BE565A777165350661296D39095492A09EF834C875E706145415814CAF81E5690B13681E4DE0D5907CE2C5F2FBA963BEC2C65A2203E3C16578F5A25D3223DE769886D9BE184A5563976587792DE0739447F26441D7569D86EC40F6615843C278B13B25FC85C6B9F8C716EAFE24EC28857EE82407883E11D970377E6E524FB9D85D3798EEB9905E19CF149CAAC8DCEB97B43F64EC0DE7712CB504A3E101844E649EC6C08178653332A2EF0248228AD9C0581AA96AAC6828442435AEC16644C0514EABD0A60CB0B1D486BE568C186A12AB077E958FA7D628D55102A02086FAE8238475F41093484AD459A5D1B1156B30B271A82249D7BA0DE2DC83A738B855990716003F13C8F989478CE7954B405093657D867430813C5B646DF681E5244E19EB711C9A226384F2F8240E9979546890990D0E06F43E018B1D9F7A568731268ABCCF53E485FAA2277410813953FBF57BE036D3E64EE2172E9DE84AA9298108787D4452D9276C9589ED228200E556C206E0F824A5FC4E61B954436E010CAB40A82AEF3DFEC5A940E3A5551AC845B460BF0BE7188D791A78980DA5243AC90D110C282CE72BEB708751AF7D828119B3C4010EE2EF539460F0A955455990777853CBEEA24B42E5D7174C53BA5BBB54F953392617B93E4BA30E5056D0E0DEC5D18BAC1F26C4D9E8886212213F57908A817E3C28B3AABADA3E2FC48CD07A23A81816B9DDAC7E898909BE336249489E9B3E24647E4C0D4CCBBCED54558266E21D4B9381DF77E87951AB137942AE673BAC55948FB8B2CA5349110F2FE85E876A1027810B4331612B00B620931C4FF5DA76519D2437C574390DB19E37189C4D05A39CBADAE49E7A02F5D47451A42C9ED2BCD6D88AB3B8C6A43A634AB5F5BF578787025F6801A22ABA110140071A647BAFDE687EA2300CA7CD3CD2F7157FD3C9F008496BEA927AF1B53E3499264301E0465B8A857EE2202558814FBB82F284646'));
DBMS_LOB.APPEND(dz_lob1,HEXTORAW('2F0C94412A08CDD0C9C98EDA48EC8306BBEB94455D65384F435C1D7478B78F899E1D19ADD710690C982019AD5A04EA91C2C4FBEC42242F50F3E536800CB41F8E3D95845D72276ABD5D858925A0345FADCB91C5FA40BA94A134919B48C22F6412394A64A457F84143AF495D7A41E0D151CB6E5171F5F8B87D5A0E1708A24AA4206B2445250B55100CA6F479464310558A269E37350286ABA11EBFCD03F84520AA357F7ECE5B08696BFA50FCE1FAAD55420E8A96F395FD10624F2AAD202E23474A2AFE747295EEA6EC5F1259E2600A3155055194B6DE2D5AD237F2C85B773BBCAEBC535505918B83883499EABB744BD9E6D99C4399092492D60D7EAAC16C59C2662E5C669BD652EA32B01B9F66FD984BE5A17BB8DA361BF8860537CA1BA9DA6997D95EE3D0AAE91681512B440CC841AF5A0DC103841A51C906BC90C81F6BF7F4318CA849790D955EAA3EFBB4477EDDA8A3EACCE743156ACBDB604D99EF0FAB0AA27EB2A4F8058163F89CF95DC555C0FB9C2E1119112C23AE757617BAD81C5EFBFA419C1CF48952F68D9C6126AEB556A26778061E762F1B0883D87AAC21901B6749F7A98D34DB504A3D1C0C8603624BF20A423F8409A24341EC3A25449956338372B4446F7653401C76CA3D5875B3DFD166D48D42B68C7CB4BF8237CAC66E410D4D7303271A8D509362A0F96E4314F045AC534D865FF2C0CB7B345386E4121FE308EBA19B60006B909108A8D1EC1A0129CC50644E4F80848D0AA0B8BC104BB3036ABF1BC2A27A60B79B26138873DA1E58E1175AEABD48486B6939DEF32A0D9C8E4F9E1E4279E180D577D1498C3A19231485704D2CB6FB36943A77C5969D8E7C7EB8D3DD5B0D9747A844590DC514B18172BB215BA2505A0AADA74C2A3DE8D50FED86951907ADDD08345B1222BF0D09CD8D63DFA2358390BFE33F089E5C8FB37992F2EFA1526B882ECD054695181ECFA1F677CED4544158719975164CF715ABAD11B80F94B0B621409A9DD6ED04E2708C564857CCF6A4EC83F93B8D8017CCAA6B7E8DAD761DDD76D154DA07EB5C3F05C188A47A66C5EDA3FAB2657396C70EDD4E6557900B431515ABA320B3B43F06DEC7C8D4F5D13D86B007C98FFABA485A10719D9E94356F7163F24E3ED6170207CEC7AA3E08CE441F144E5152DCE0C9A4DDBE4BA7F4564EB312C4369FC941E3504D90A0106DAFC6ABF9179F4E6C4AB215212485CE301BC24E18372F25D5048654A23B51103C26100C45160437EC4E12AD21C484FFDE9AB7700C9AE893AC3CC827A79A499E8F03227555C75B0D91CED254AACCE4BDF5918749A8204BFD9E0AEA1BE169A0833E4D3444B3B51CEF3B8BDC1F85AA433FC8D4F84543992582DC5E631EE5761BB2B1F9188E8A0BEFBBDC486F83107F3EA1944C72347944C2ED'));
DBMS_LOB.APPEND(dz_lob1,HEXTORAW('0DE1324C2D15375494E594B6464073CE623DA7BA4C018189FA3AC2FA341B6C8F64F11702BF14272FEFFA7165161FC579100EBA55D45544AA1834E1B6A76123C22A7BE7F16525F32F4DB6E60F7973DACBB4B29DE23225441FAAA09070FA9486DE5630AE8823D4A7A4FA80B264B609973584B4E5E72BC62F22D48AF13EF5E523E847740EB85E9F581879649E66CF539F718502A6BF3F3A99FCEE522F68BD458C4256B4F63168821068B62D044D7989FBF5B9ED3278689E688D63FAE05CFAA4D59FDBAE9ACCED6F46FFFFD1F4E75FBE7F22287F150000'));
DBMS_LOB.CREATETEMPORARY(dz_lob2,TRUE);
DBMS_LOB.APPEND(dz_lob2,EMPTY_BLOB());
INSERT INTO foo
(OBJECTID,FEATUREID,SOURCEFC,GRIDCODE,AREASQKM,WBD_HUC12,WBD_HUC12_PERCENT,SHAPE,SE_ANNO_CAD_DATA)
VALUES
(6,14783739,'NHDFlowline',1126275,1.1781000174425,'071200040104',1,
dz_sdotxt_main.geomblob2sdo(dz_lob1),dz_lob2);
END;
/

Installation

Simply execute the deployment script into the schema of your choice. Then execute the code using either the same or a different schema. All procedures and functions are publically executable and utilize AUTHID CURRENT_USER for permissions handling.

Collaboration

Forks and pulls are most welcome. The deployment script and deployment documentation files in the repository root are generated by my build system which obviously you do not have. You can just ignore those files and when I merge your pull my system will autogenerate updated files for GitHub.

Oracle Licensing Disclaimer

Oracle places the burden of matching functionality usage with server licensing entirely upon the user. In the realm of Oracle Spatial, some features are "spatial" (and thus a separate purchased "option" beyond enterprise) and some are "locator" (bundled with standard and enterprise). This differentiation is ever changing. Thus the definition for 11g is not exactly the same as the definition for 12c. If you are seeking to utilize my code without a full Spatial option license, I do provide a good faith estimate of the licensing required and when coding I am conscious of keeping repository functionality to the simplest licensing level when possible. However - as all such things go - the final burden of determining if functionality in a given repository matches your server licensing is entirely placed upon the user. You should always fully inspect the code and its usage of Oracle functionality in light of your licensing. Any reliance you place on my estimation is therefore strictly at your own risk.

In my estimation functionality in the DZ_SDOTXT repository requires the full Oracle Spatial option for 10g, 11g and 12c. Note this is primarily due to the functions that manipulate the MDSYS.SDO_GEORASTER and MDSYS.SDO_RASTER types.