Toad 13.3, Bug joining tables with identical columns and one xmltype column

Toad for Oracle
1

I've found a bug inolving Toad 13.3. If you are having two tables, both of them having at least one identical column and one table has one xml column, after joining them you will receive ORA-00904

Test case

drop table t_start;
drop table t_stop;

create table t_start(id number, start_time date, cx1 xmltype);
--create table t_start(id number, start_time date, cx1 varchar2(100));

create table t_stop(id number, stop_time date);

select * from t_start ts, t_stop tp where ts.id = tp.id;

-- Timestamp: 12:02:19.603
Select T.ID, T.START_TIME, T.CX1.GetClobVal() as CX1, T.ID_1, T.STOP_TIME
From
(
select * from t_start ts, t_stop tp where ts.id = tp.id
) T;
-- ORA-00904: "T"."ID_1": ungültiger Bezeichner

Happens only with xml columns

Oracle Client 18.3 (instantclient-basic-windows.x64-18.3.0.0.0dbru)
Toad 13.3 64 Bit.

Regards,
Alex

2

Hi Alex,

This error has been logged already. It was recently brought up with support.

In prior versions, the XMLTYPE column would have caused an access violation and crashed Toad.

Toad uses the OCI in the unicode-enabled mode, where XMLTYPE unfortunately doesn't work very well and can cause access violations. Starting in Toad 13.3, we rewrite the SQL, pushing your query it to a subquery, and converting the XMLTYPE column with GetClobVal to prevent the access violation.

Unfortunately, this situation you've found is still a problem.

As I'm sure you're aware, the way to work around this is to specify your field names, leaving off (or aliasing) the duplicates column names. Or leaving out the XMLTYPE columns.

Thanks for reporting it.

3

Can you perhaps "enhance" the Toad Option for XMLColumns with "none", means "no Preview" or something like this? I adding myself the getclobval where needed and it works. Waiting for the next Release!

Regards,
Alex

4

Well, if there is only one table, then you can see and even edit the XMLTYPE data (if you selected ROWID), so if I just don't show the data, then that will be a problem for other users.

I should be able to expand the "Select * from Table1, Table2...TableN" query to include field names with aliases and prevent the error. I'll post back here when it's fixed.

5

Ok, it will be fixed in the next beta (on or about May 11th, 2020)