0

Our .NET application uses ODP.NET with SDO_GEOMETRY which is a UDT for spatial functionality. Currently only the Oracle Unmanaged Driver supports UDT for use in ODP.NET. Although it looks like UDT support is definitely on the ODP.NET team's radar.

What I'm wondering is if there is a manual way to convert in and out of UDT based on a byte[] etc. I know in the link I posted above that people are using WKT as a workaround, but we'd also be willing to entertain the idea of writing our own adapters for C# SDO Geometry, SDO_Point, etc. that have something like ToBytes() and FromBytes() if they could be used in a query.

Is it possible to cast the SHAPE attribute as a BLOB and then write some C# to deserialize it? Perhaps you would have to write your own PL/SQL functions to convert BLOBS to SDO_Geometry?

Because of the convenience of the managed driver, we would like to be able to use it, so we don't have to compile against multiple versions of the unmanaged driver and work within client environments that all have varying unmanaged client configurations.

The last fallback would be to pick one version of Oracle client and just require the customer to have that version of the unmanaged client installed, but they'd need to be aware of any gotchas that come with multiple side-by-side installations (PATH, GAC, machine.config, etc.)

Improve this question
5
  • procedure/function-wrapping the UDT=>byte would require quite a bit of pl/sql-programming, but should work without environment-dependencies (you define your byte-structure and implement the conversion). if the data-sets are huge probably worth the effort. but if sdo_geometry is not large you could convert to xml (which is easy out-of-the box) "XMLTYPE(sdo_geometry_var).getstringval" in the wrapper Commented Jul 16, 2020 at 15:08
  • I've used xml to pass complex structures. Not sure is Oracle has JSON parser for the similar function Commented Jul 16, 2020 at 15:19
  • @MichaelHauptmann thanks for the suggestion. that makes sense about 'you' defining the byte structure and conversion. I didn't know XMLTYPE existed either. That might make for a quicker coding effort, but as you mentioned, if the geometry objects are huge, it could be worth considering another solution. Appreciate the thoughts! Commented Jul 16, 2020 at 17:06
  • spatial should be huge by nature ... didn´t work with that kind of data. maybe blogs.oracle.com/author/tom-kyte / asktom.oracle.com or stevenfeuersteinonplsql.blogspot.com can put in some thought about memory-issues in this kind of conversion (or did in the past) Commented Jul 16, 2020 at 17:55
  • Just got done with a POC. I changed my select statement to convert the sdo_geometry to the WKB (well-known binary) format using sdo_util.to_wkbgeometry(...) and sdo_util.from_wkbgeometry(...) and it turns out we already had code to convert WKB format into objects we can work with. Thanks for the suggestion, worked nicely! Commented Jul 22, 2020 at 22:54
Related questions
5
c# datatypes -> oracle datatypes
9
How do I map from Oracle UDTs with ODP.NET without using OracleObjectMappingAttribute?
3
Oracle .NET Data Provider and casting
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.