Toad World® Forums

Toad not generating dummy names for select * when you have xmltype

In previous versions of Toad a query such as select col1, tab1.* from tab1 would give you col1 from tab plus all the tab1 columns. This is useful for adhoc queries in a table with a large number of columns if you want to present certain priority columns but may see the remaining columns. The duplicated column(s) would be shown with column name col1_1.
Having upgrade to Toad 14.0.75.662 this is giving an error when there is an xmltype column on the table.

In its simplest form on a table with no data
create table tab1 (
col1 INTEGER,
col2 XMLTYPE);
--insert no data

This works:
select col1, col1 from tab1

But this fails
select col1, tab1.* from tab1
-- Gives error
[Error] Execution (142: 16): ORA-00904: "T"."COL1_1": invalid identifier

Is this a bug?
Thanks
Neal

Yes, there are some issues with queries involving XMLTYPE columns.
See, for example, one of the Support Knowledge Base articles here:
https://support.quest.com/toad-for-oracle/kb/331427/getting-errors-ora-00904-or-ora-00936-when-executing-a-query-with-xmltype

Waiting for fix in one of the future releases.

Thanks. I can reproduce this and will log it. I thought it was fixed for 14.2 but I see that it is not.

You can work around it like this:

select col1 as col1_the_great, tab1.* from tab1

Thanks @JohnDorlon