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

Toad for Oracle
1

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

2

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.

3

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

4

Thanks @JohnDorlon

5

Is there any ETA on a fix? I am having the same issue. I work with many tables and many joins and like to do select a., b., c., d., etc. Now.. I have to list all columns for every table and remove columns with the same name. Very time consuming.

6

Hi Neil,

This was fixed a long time ago. What are your Toad, Oracle server, and Oracle client versions?

7

Hi John

I am using Toad for Oracle version 13.2.0.181 against Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 and Oracle Client Version is OraClient19.

8

Hi Neil,

Ah, yeah that's the problem. I want to say it was fixed around version 15 or so, so I will. It was fixed around version 15 or so. :smiley:

If you can, upgrade to the latest....16.3.

There isn't really any workaround for that in 13.2, I'm sorry.

Edit: This was fixed in version 15.1

-John