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 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 (
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?

Yes, there are some issues with queries involving XMLTYPE columns.
See, for example, one of the Support Knowledge Base articles here:

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