Error with XMLTYPE

[jdorlon]

Hello,

I am using Delphi 2009, ODAC 6.9.0.58, Oracle 11.2.0.1 client and server. In ODAC, OraCall.OCIUnicode := True and OraSession.Options.UseUnicode := true;

When I create this table:

CREATE TABLE test_table2 (xmltext XMLTYPE)
XMLTYPE COLUMN xmltext STORE AS BINARY XML;

INSERT INTO test_table2
VALUES (xmltype ('hello'));

COMMIT;

then try to select from it using a TSmartQuery, I get an access violation. It works OK from SQL Plus, and from an older build of our application that used Delphi 7 and ODAC 6.25, with no unicode.

Is this an ODAC bug?

[bork]

Hello

The error is generated by OCI function OCIPStreamRead if Unicode OCI environment is set. It seems like the OCIPStreamRead function cannot return XML if Unicode OCI environment is turned on and XML is stored as binary XML.

If you want to use the XMLTYPE type in you tables then you should set the OraCall.OCIUnicode variable to False or declare table column without the "STORE AS BINARY XML" declaration.

[jdorlon]

Thank you for the reply. Unfortunately, the workarounds are not options for me. Our application does support unicode, and the tables are created by the users, so I have no control over them.

[bork]

I offer you several ways to resolve this issue:
- Set the OraCall.OCIUnicode variable to False
- Declare table column without the "STORE AS BINARY XML" declaration
- Modify your Select statement: select cast(xmltext as varchar2(1024)) xmltext from btest26
- Store your XML as CLOB or NCLOB (for storing in the Unicode charset)

If no solutions seem appropriate for you and you don't want to modify your application then you can ask the Oracle company to fix the OCIPStreamRead function for case when OCI client environment was initialized by the OCIEnvCreate function with value OCI_UTF16. As soon as the Oracle company fixes the OCIPStreamRead function you can read your XML without errors.

[jdorlon]

Thabk you for the additional information. I will open a case with Oracle. I just wish Oracle's support forum were as simple as this one!

[jdorlon]

Hello,

I have opened a case with Oracle on this. They will not investigate the unless I can "submit a pure OCI testcase that demonstrates the problem".

This would probably take me a while to put together. You don't happen to have a sample program like this handy, do you? The program just needs to run "select * from test_table2" with unicode enabled.

they also had this to say:

OCIPStreamRead does not appear to be a publicly documented OCI API. So most likely another public OCI API is calling that one privately. Just another factor to be aware of which will most likely complicate creating a testcase as you will need to mimic the public OCI API calls to reproduce this error.

Thanks,

John

[bork]

Hello

OK. We will send you a small sample application to reproduce this issue. But it will take some time.

[jdorlon]

Thank you, that would be very helpful and I appreciate it!

[jdorlon]

Hello,

It's been almost 5 years. Can you find some time to build that sample app for me? A lot of our customers would really appreciate it.

Thanks,

John

[m.ghilardi]

I also need this fix.
Unless I set UseUnicode = false, I can't pass an OraXML as Input parameter of a TOraSQL without raising an Access Violation

[AlexP]

We cannot reproduce the problem. Please provide the code you are using for data insertion.

[m.ghilardi]

Hello AlexP,

I sent you an email with a project that reproduces the problem.

[AlexP]

In the example you've sent, AV does not occur (in both cases). When you run the application, the error «OCI-31011: XML parsing failed» emerges.

[m.ghilardi]

I re-tested the project, OCI-31011 is not raised. Please be sure to have installed all the queues and the types in the SQL that I included inside in ODACQueue2.zip

Here is the environment I use


Oracle Linux
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Client: 12.1.0.1.0
(Windows 7)

ODAC: 9.4.13

IDE:
Embarcadero® RAD Studio XE4 Version 18.0.4905.60485

[AlexP]

Please try to reproduce this behavior on the latest version 9.5.18 and let us know the results.