Beta .110 XMLTYPE columns must be cast to CLOB using .GetClobVal()

Toad for Oracle - Beta
1

Database is 11.2.0.4, character set WE8MSWIN1252, NCHAR character set AL16UTF16. The table in question contains two XMLTYPE columns.

In the .110 beta, a query such as select * from mytable returns the error:

"XMLTYPE columns with binary storage must be cast to CLOB using .GetClobVal() or XMLSerialize."

Data from this same table can be viewed through the Schema Browser data tab without error. Both the query and data tab work as expected in Toad 13.1.0.78.

Regards,
Doug

2

The SB automatically adds GetClobVal.

Did "Select * from mytable" work in the editor in 13.1 on that table w/o producing an error?

In Editor, you can now do "edit mytable" and you won't get that message, and XMLTYPE columns will be editable in the popup dialog.

3

Hi John,

The select works without error in 13.1.

We will try "edit mytable" and get back to you.

Doug

4

I just tested with WE8ISO8859P15 and AL16UTF16. That's the closest I have at the moment to yours, but I'll create one like yours tomorrow.

For me, if the XMLTYPE's storage type is CLOB, no problems. If the XMLTYPE's storage type is Binary XML, Toad throws an AV. It is this AV that I am trying to avoid with the message about GetClobVal and XMLSerialize. If I need to not perform that check for certain character sets, I can do that.

Toad is checking the column with a query similar to this:

select column_name, storage_type
from dba_xml_tab_cols
where owner = ...
and table_name = ...

please confirm that your table in question is stored as Binary.

by the way you can add a where and/or order by to "edit mytable" if you want.

Thanks.

5

Also, please send me your Oracle client version, and if it's 32/64 bit.

And another thing I just thought of - does the XMLTYPE column contain data or is it all nulls?

I am very interested in trying to reproduce this.

6

Running all 64-bit: Windows 10, 18.3 Instant Client and Toad.

The first XMLTYPE column has both null and not null values and is stored as CLOB. The second column is null for all rows and stored as BINARY (probably not intentionally).

7

OK, nulls is the key. I don't get an error if the Binary XML column has only nulls either. If you stick some data in there, I believe you'll get an AV in Toad 13.1 when you try "Select *" from that table.

It's fair to assume that XMLTYPE columns will have data, so I think this change is justified. I have answered countless forum posts about AVs when users try to select XMLTYPE. Toad crashes and they lose work. There is no more lost work due to XMLTYPE crash with this change. Toad can't support XMLTYPE with binary storage due to an OCI limitation/bug when in unicode mode, which Toad uses.

Here are some options for you if you want to be able to "Select *" from this table

  • Drop the column and and re-add as XMLTYPE with CLOB storage (you can get away with this because all data is null, but column position will move to the end)
  • Use DBMS_REDEF (Toad has a wizard for this if you have DBA Module)
  • Use Toad's rebuild table wizard (supports XMLTYPE, and will work just find as long as no other users will attempt to access the table during the process). If you go this route, you have to send the script to the editor and modify it so the XMLTYPE column with binary storage will be created with CLOB storage.

If you want help with any of that, let me know, I'll be happy to assist.

8

Oooh, I think I can made XMLTYPE "just work".

Watch the notes in upcoming betas.

9

Would be awesome if you could. :slight_smile:

10

A few minor hurdles to overcome first but it seems doable.