DBMS_XMLGEN.GETXMLTYPE causes Toad to become unresponsive

Toad for Oracle
1

I am using Toad for Oracle version 12.0.0.61 with Oracle version 12c.

I am using Toad to write some queries that work with Oracle data dictionary views that use deprecated LONG data types, like the view 'all_views'. This would probably be moot if Oracle didn't insist on using data types in their own internal tables that they refuse to support or allow anyone else to use. But I digress. I am using DBMS_XMLGEN.GETXMLTYPE to convert these LONG columns to XML data that I can work with. The problem I frequently encounter is that if I attempt to execute a query that uses GETXMLTYPE and results in an XML parsing error, any subsequent execution of even the simplest query will cause Toad to become completely unresponsive and I will lose any unsaved work. This is easily verifiable and repeatable every time.

  1. Try the following query:

WITH xml AS
(
SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT view_name, text FROM all_views WHERE view_name IN (''ALL_PART_TABLES'',
''USER_LOB_PARTITIONS'',
''ALL_LOBS'',
''ALL_IND_PARTITIONS''
)') AS xmldoc FROM dual
)
SELECT * FROM xml;

  1. Observe the following error:

OCI-31011: XML parsing failed

8611.Toad_Error.png
8611.Toad_Error.png1047×333 6.28 KB

  1. Press OK to clear the error.

  2. Run the following query:

SELECT 1 FROM dual;

  1. Observe that Toad freezes and becomes completely unresponsive indefinitely.
2

Unfortunately, XMLTYPE support has been an ongoing problem/known issue.

To get around it, you need to cast your XMLTYPEs to clobs. you can do it with your sample statement like this:

WITH xml AS
(
SELECT DBMS_XMLGEN.GETXMLTYPE(‘SELECT view_name, text FROM all_views WHERE view_name IN (’‘ALL_PART_TABLES’’,
‘‘USER_LOB_PARTITIONS’’,
‘‘ALL_LOBS’’,
‘‘ALL_IND_PARTITIONS’’
)’).GetClobVal() AS xmldoc FROM dual
)
SELECT * FROM xml;

3

That was exactly what I needed. Thanks!

4

Hmm, funny. Mike, I tried your query in Toad 12.10, and while it takes 5-6 seconds I’m getting a ROWSET of about 340 lines (using a remote 11g !)

ALL_PART_TABLES select u.name, o.name, decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 'UNKNOWN'), po.partcnt, mod(trunc(po.spare2/65536), 65536), po.partkeycols, ...

The one with GetClobValue() executes in less than a second (getting a HUGECLOB).

Andre