Error "Access violation.. in module orageneric11.dll..." when querying a table with an XMLTYPE column (4244824)

Error "Access violation.. in module orageneric11.dll..." when querying a table with an XMLTYPE column

When attempting to query a table with XMLTYPE as one of the column datatypes, the following error appears:

"Access violation at address 6053BE83 in module orageneric10.dll. read of address 00000008."  or

 "Access violation at address 148DF589 in module OraClient11.Dll. Read of address 00000008" or

"Access violation at address 000007FEDE9CC8BA in module 'oraclient12.dll'. Read of address 0000000000000010"

The data in the table is valid and also the error can occur, even if there is no data in the table.

 

In addition, version 12.9 can also hang, after the Access Violation, requiring Toad to be killed.

XMLTYPE is not supported in Toad 10 and upwards, due to the conflict with the OCI unicode flag. The error is coming from Oracle and is related to the OCI unicode flag. This is enabled by Toad for general unicode support but this also brings up the OCI's conflicts with XMLTYPE. The issue generally only occurs if the XMLTYPE column is stored as a binary and is not specific to the Oracle Client version used. 

WORKAROUND 1:

Omit the XMLTYPE column on the select or use the getCLOBVal() function to view this column.

The select with the getCLOBVal() function would be like this:

select y.XMLTYPE_COLUMN.getCLOBVal() from EXAMPLE_TABLE y;

where EXAMPLE_TABLE is the table with the XMLTYPE column and XMLTYPE_COLUMN is the XMLTYPE column.

Note if there is a table with a lot of columns and you can't do 'select * from' because of getCLOBVal, then you can get the full 'select * from' query from the Schema Browser:

 

WORKAROUND 2:

Try running the query one or more times, the data may display after 3 query attempts. (This workaround does not apply version 12.9, as Toad hangs after the access violation

STATUS:

This issue has been fixed in Toad Data Point 13.2.  This version can be downloaded from the downloads section of our website, which you can access by clicking here.

** Note: This change request is for handling the access violation and subsequent hanging. This will not be for the underlying issue, which is an Oracle issue.

 

TOR-1008

SQL*Plus does not support Unicode so they do not enable the Unicode flag and will not encounter an error. XMLTYPE is not supported in Toad 10 and upwards, due to the conflict with the OCI unicode flag. A lot of the functions in the OCI come in two versions - one being unicode-enabled and the other non-unicode-enabled. This bug only happens in the unicode-enabled functions. SQL*Plus uses the non-unicode-enabled versions of these functions. This is why it works in SQL*Plus but not Toad. Our Development team have talked, more than once, to both Oracle and DevArt (the vendor of ODAC, which is our 3rd party library which accesses the database) about this. Devart says that it is a bug in the OCI, and there is nothing that they can do. Oracle says that the unicode-enabled functions that we are using to fetch XML data are not even documented, so if they don't work, then it's technically not a bug. The workaround is to query XMLTYPE columns using the GetClobVal function, like this: select t.my_xml_column.GetClobVal() as my_xml_column from my_table t The table alias (in this case, T) is required for GetClobVal to work. The Schema Browser-Tables-Data tab will automatically build the query this way. You have to include it manually in the editor.