PSD and DOA can't cope with XMLTYPEs properly. We've kept running into hangs, access violations and wrong results (NULL/<Value Error>) for years. We've tried to ask for help here many times, I tried to offer my help. We've been mostly neglected and advised to either use other Oracle client (OCI) or use XMLTYPE.getClobVal(). I lost my patience and patched the PSD/DOA binary. Since these programs aren't open source, developing and applying the patch for different versions is quite cumbersome. I probably won't do that. But here's my help for any competent programmer to do the right thing.
XMLTYPE is not very well documented in OCI. SQL*Plus uses reserved functions to access XMLTYPEs. Application programmers are advised to use the XDK api. The former is simpler and faster and uses less memory. The latter needs version dependent dll's (eg. oraxml11.dll) that are not installed with Oracle Instant client. PSD/DOA executes a small PL/SQL block for every XMLTYPE displayed as <CLOB> to convert the XMLTYPE to CLOB.
Here are the main problems:
1. OCIDefineByPos()
"indp (IN) pointer to an indicator variable or array. For scalar datatypes, pointer to sb2 or an array of sb2s. Ignored for SQLT_NTY defines. For SQLT_NTY defines, a pointer to a named datatype indicator structure or an array of named datatype indicator structures is associated by a subsequent OCIDefineObject() call."
Despite this specification, PSD7/DOA tries to use indp to detect NULL values and comes up with false positives.
2. OCIObjectFree()
While I've not found a warning in the OCI specification, this must not be called for NULL objects (XMLTYPEs), as it will cause access violations.
3. Passing the unread XMLTYPE to the aforementioned PL/SQL block as a bind parameter. I've not seen anyone else doing this. This used to work with basicfile clob storage. But with 11.2, the default storage is securefile binary xml. Executing the block with the latter causes ORA-01013. Since error handling is not a strong point of PSD, it will crash. Whoever has the time may open an SR for this ORA-01013 at Oracle support. I'd rather spend my time with something productive.
So what's the solution? Using OCIXmlDbInitXmlCtx, XmlSaveDom and OCIXmlDbFreeXmlCtx. Or use undocumented OCIPStream... functions. Both work.
Here's my profiling for selecting 512 rows with XMLTYPE column:
- DOA's way: 9 seconds, 64 Mebibyte memory allocated for PSD;
- Using XmlSaveDom: 16 s, 85 MiB;
- Using OCIPStream: 4 s, 62 MiB.
It's questionable whether it's worthwhile to convert all XMLTYPEs to String->Variant when only a <CLOB> placeholder is displayed. (I've changed that placeholder to <XMLTYPE>, though.)
