Issue with XMLTYPE and OCIUnicode mode.

[MarkF]

Using ODAC 9.0.2 with OCIUnicode = True I get the following error when I query a table that contains an XMLTYPE column:

Project x raised exception class $C0000005 with message 'access violation at 0x0764d7ed: read of address 0x00000008'.
and then:
Access violation at address 0764D7ED in module 'OraOCIEI11.dll'. Read of address 00000008

This used to work, but I'm not sure when this issue started. I'm testing on an Oracle 12 database (both with the 11.2 and 12 32bit clients.)

Here's my test table and query:

Code: Select all

create table xml_test (id number(38) primary key, x xmltype);
insert into xml_test values (1, '<data>Test 1</data>');
insert into xml_test values (2, '<data>Test 2</data>');
insert into xml_test values (3, '<data>Test 3</data>');
insert into xml_test values (4, '<data>Test 4</data>');
insert into xml_test values (5, '<data>Test 5</data>');
commit;
select * from xml_test;

Thanks for any help.

-Mark




I don't use XMLTYPE very often, so I'm not sure when this

[AlexP]

Hello,

We cannot reproduce the problem on the table you have provided and Oracle 12. Please send a small sample to Alexp*devart*com, also please specify the exact versions of the IDE and Oracle client and server.

[MarkF]

Hi Alex,

Here's the code (it's as simple as can be, the only thing is that OCIUnicode must be on as I mentioned.) This is with Delphi XE4, ODAC 9.0.2, Oracle clients 11.2 and 12, 32bit (I haven't tried 64bit yet.)

Code: Select all

program XMLTypeTest;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  OraCall,
  Ora;

var
  FOraSession: TOraSession;
  FOraQuery: TOraQuery;
begin
  try
    { TODO -oUser -cConsole Main : Insert code here }
    OraCall.OCIUnicode := True;
    OraCall.OCIDLL := 'D:\Oracle\oci.dll';
    FOraSession := TOraSession.Create(nil);
    try
      FOraSession.ConnectPrompt := False;
      FOraSession.Options.UseUnicode := True;
      FOraSession.ConnectString := 'SCOTT/tiger@//192.168.11.112/orcl.mfm2';
      FOraQuery := TOraQuery.Create(nil);
      try
        FOraSession.Connect;
        try
          FOraSession.ExecSQL('drop table zxml_test');
        except
          // Eat this error.
        end;
        FOraSession.ExecSQL('create table zxml_test (id number(38) primary key, x xmltype)');
        FOraSession.ExecSQL('insert into zxml_test values (1, ''<data>Test 1</data>'')');
        FOraSession.ExecSQL('insert into zxml_test values (2, ''<data>Test 2</data>'')');
        FOraSession.ExecSQL('insert into zxml_test values (3, ''<data>Test 3</data>'')');
        FOraSession.Commit;
        WriteLn('Connected');
        FOraQuery.SQL.Text := 'select x from zxml_test';
        // project XMLTypeTest.exe raised exception class $C0000005 with message
        // 'access violation at 0x03bdd7ed: read of address 0x00000008'.
        FOraQuery.Open;   // Should fail here with above message or similar.
        while not FOraQuery.Eof do
        begin
          WriteLn(FOraQuery.Fields[0].AsString);
          FOraQuery.Next;
        end;
      finally
        FOraQuery.Free;
      end;
      ReadLn;
    finally
      FOraSession.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.

-Mark

[AlexP]

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.

I offer you several ways to resolve this issue:
- Set the OraCall.OCIUnicode variable to False
- Modify your Select statement: select cast(x as varchar2(1024)) x from zxml_test
- Store your XML as CLOB or NCLOB (for storing in the Unicode charset)

[MarkF]

Hi Alex,

Thank you for duplicating the issue. None of those work arounds are a long term solution to this problem. Retrieving XMLTYPE columns with OCIUnicode on used to work fine, so this is a change/bug that's been introduced recently in the ODAC code. I'm not sure exactly what version it started, but I generally upgrade quickly to new versions so it must be recent. Please look into this deeper.

-Mark

Another thought:
Is it possible for ODAC to bind that type as text instead? There doesn't really seem too much to lose since it doesn't work at all now with OCIUnicode on.

[AlexP]

This problem was always there, but it occurred not in every case. Unfortunately, we cannot definitely identify the reasons for such behaviour.
You can cast such field to a line in the query using the CAST operator.

Code: Select all

select cast(x as varchar2(1024)) x from zxml_test

[MarkF]

No, I'm asking if it's possible for ODAC to internally retrieve XMLTYPE as a string instead of trying to deal with it as an object type. There doesn't seem to be anything to lose in doing this since ODAC cannot currently handle XMLTYPE at all with OCIUnicode on.

It would be helpful if you could work on fixing this issue.

-Mark

[AlexP]

Hello,

For the time being, there are no methods for retrieving XML as string in ODAC. We will consider the possibility to add DataTypeMapping for conversion of XML to string in one of the next versions.

[MarkF]

Hi Alex,

Thanks, that would be helpful. It would also be nice if you could work out with Oracle what the actual problem is and fix it.

-Mark

[MarkF]

Hi,

I'm just wondering if this has been worked on at all? Any help is appreciated!

-Mark Ford
Benthic Software

[AlexP]

Unfortunately, we haven't found a method for retrieving XML as String without working with an object, therefore such functionality cannot be implemented yet.

[MarkF]

Thanks for the update. Is OCIPStreamRead an undocumented OCI function? I can't find it in Oracle's OCI guide. If so, is there an alternate or more official way to retrieve XML?

-Mark

[AlexP]

Yes, we are using these undocumented functions for work with XML. We haven't yet found more optimal solutions for work with XML

[jdorlon]

Hello,

A similar problem - Using the code already on this thread, in ODAC version 9.4.14, I would initially get the access violation, but then if I closed the query and re-opened it, the data would appear.

Now I have upgraded to 9.6.20. I still get the initial access violation, but when I close and re-open, the XML field returns a bunch of ???????? characters. A similar test in a GUI will give characters that look Chinese. Is there any way to get it working at least as good as 9.4.14 again?

The only thing that I changed in the app (besides the DLL and connection info) is adding the bolded lines

try
FOraQuery.Open; // Should fail here with above message or similar.
except
end;
FOraQuery.Close;
FOraQUery.Open;

I am using Delphi XE2, Windows 7, Oracle client and server versions 12.1.0.2. Compiling in 32 bit.

Thanks

John

[AlexP]

Please send the full sample demonstrating the problem to support*devart*com