search

oracle / odpi

ODPI-C: Oracle Database Programming Interface for Drivers and Applications
https://oracle.github.io/odpi/
Other
264 stars 75 forks source link

XMLTYPE as "real" XML #162

Open daviessm opened 3 years ago

daviessm commented 3 years ago
  1. Describe your new request in detail

I would like to be able to read XMLTYPE data as XML without having to re-parse it from a string. Of course, this would need to be able to return data larger than VARCHAR2(4000) as well.

  1. Give supporting information about tools and operating systems. Give relevant product version numbers

My use case for this would be through implementation in rust-oracle in both Linux and Windows.

cjbj commented 3 years ago

Noted. Historically the XML team hadn't exposed necessary APIs through the Oracle Client C libraries, but that may have been resolved.

kubo commented 3 years ago

XML APIs seem to be available at least since Oracle instant client 11.2.0.4.

I tested it using this code. It is compilable since Oracle instant client SDK 18.3 as it is. Oracle instant client SDK 11.2.0.4 doesn't include necessary header files. However it is compilable by copying orastruc.h, xmlerr.h and xmlproc.h and it runs fine.

The code inserts the following XML and fetches it. The total size is about 640k.

<data>
  <data>xxxx ... omitted (the size is 64k - 2) ...xxx</data>
  <data>xxxx ... omitted (the size is 64k - 2) ...xxx</data>
  <data>xxxx ... omitted (the size is 64k - 2) ...xxx</data>
  <data>xxxx ... omitted (the size is 64k - 2) ...xxx</data>
  <data>xxxx ... omitted (the size is 64k - 2) ...xxx</data>
  <data>xxxx ... omitted (the size is 64k - 2) ...xxx</data>
  <data>xxxx ... omitted (the size is 64k - 2) ...xxx</data>
  <data>xxxx ... omitted (the size is 64k - 2) ...xxx</data>
  <data>xxxx ... omitted (the size is 64k - 2) ...xxx</data>
  <data>xxxx ... omitted (the size is 64k - 2) ...xxx</data>
</data>

Note that the maximum size of one text node seems "64k - 2". If the size (see here) is changed to "64k - 1" or bigger, it crashes by segmentation fault.

The 64k limitation may be overcome by Orastream APIs. In https://docs.oracle.com/en/database/oracle/oracle-database/19/caxml/package-Orastream-API.html

Orastream APIs support handling of text and binary nodes that exceed 64K in an XML document.

cjbj commented 3 years ago

There might have been some historic 'thunking' issues on Windows, possibly fixed in 18.1 (with various backports). Overall the topic is a bit murky and would take some effort to productize. Aside from direct API support @anthony-tuininga logged at least one enhancement request to the XML team for default conversion improvements; I can see this is still open.