ORA-00918: column ambiguously defined

burasami · June 8, 2011, 8:03am

Hi All,

I am using Toad 8.6.0.38 in windows 7 (32 bit).
Through toad i am connecting serveral oracle server(10g, 11g) its working fine.
When i am connecting to particular oracle user and started to browse oracle table objects its giving pop up error message

In other machines with same version its working without any error…

please help me to resolve this issue…

Thanks & Regards
Sami

HillbillyToad · June 8, 2011, 8:03am

The only way we can help you to resolve the issue, is to advocate for you to
upgrade your Toad.

It’s really hard for us to support software that’s 6 years old.

If you are interested in seeing why the problem is occurring, turn on the
‘Spool SQL to Screen’ feature. You’ll see the query that Toad
is sending that results in the ORA-00918

flyfishpanfish · June 8, 2011, 8:28am

Maybe the user is not granted same powers across all yourdatabases so in the database giving the “ambiguously defined” errorthe user is able to select a table of the same name that appears in more thanone schema? Try prefacing your tablenames with schema names and see if error goes away.

Try something like:

Select A.column_one,
B.column_one
from schema_A.table_one A,
schema_B.table_one B

Guest · June 8, 2011, 8:28am

Yeah, grants may be involved since those affect what data dictionary views Toad
uses, among other things. But the basic problem is using a version of Toad built
for Oracle 9i on an 11i database. Oracle probably added a column to one of the
data dictionary views being used in a query, and the old Toad query didn’t
specify a table alias for some columns. (I keep trying to convince myself to
always do that in my code, but I’m often not disciplined enough.)

There’s no real fix for Toad 8.6 in a situation like that. And this won’t be the
only problem that appears.

The reasonable solution is to upgrade to a version of Toad that supports the
version of the database you’re using. (Or downgrade the database back to Oracle
9i, I guess.)

– jim

JohnDorlon · June 8, 2011, 8:28am

Right. And if you tell us which query it is (using spool sql as Jeff suggested),
I can tell you what option to uncheck in Toad to avoid the error.

burasami · June 8, 2011, 8:42am

Hi Flyfishpanfish,

I agree with, When we are running as SQL Query then we have to do as you said.
But I am simply browsers a single table–>script tab through toad. I am getting this error on particular oracle user(11g) on particular machine.

When i am connecting other oracle user(10g, 11g) from other machine where Toad is installed its working fine.

Thanks & regards
Sami

Guest · June 8, 2011, 10:52am

Hi Sami,

Message from: burasami script tab through toad. I am getting this error on
particular oracle user(11g) on particular machine.

When i am connecting other oracle user(10g, 11g) from other machine where
Toad is installed its working fine.

The tough part about this problem is that you've upgraded two of the three
parts of the software puzzle -- Windows client and Oracle database -- but
neglected to include Toad as part of your upgrade strategy.

Windows DLLs, Oracle DLLs, and the Oracle database dictionary have all
changed (in some places, dramatically!) since Toad 8.

Whatever the outcome of your investigation, it would be a very very good
idea to upgrade your Toad. I believe 10.6 is the latest and 11 is so very
close to being GA now.

Good luck!

Rich -- [TeamT]

Disclaimer: I have never tweeted any kinds of pictures of myself to anyone.

NormTeamT · June 8, 2011, 11:22am

On 08/06/11 16:52, Rich Jesse wrote:

Disclaimer: I have never tweeted any kinds of pictures of myself to anyone.

I have never tweeted!

--
Cheers,
Norm. [TeamT]

burasami · June 9, 2011, 1:15am

Hi Hillbilly,

Here is SQL Spool…

SESSION: abc@LOCAL
Timestamp: 10:22:48.498
Select TABLE_NAME
FROM SYS.USER_TABLES T, SYS.USER_QUEUE_TABLES Q
WHERE 1=1
AND (T.TABLE_NAME = Q.QUEUE_TABLE OR
T.TABLE_NAME = ‘AQ$_’ || Q.QUEUE_TABLE || ‘NR’ OR
T.TABLE_NAME = 'AQ$’ || Q.QUEUE_TABLE || ‘H’ OR
T.TABLE_NAME = 'AQ$’ || Q.QUEUE_TABLE || ‘I’ OR
T.TABLE_NAME = 'AQ$’ || Q.QUEUE_TABLE || ‘T’ OR
T.TABLE_NAME = 'AQ$’ || Q.QUEUE_TABLE || ‘_S’)
and queue_table = ‘WF_WORK_ITEM’

SESSION: abc@LOCAL
Timestamp: 10:22:48.599
Select name
from sys.user_snapshots
where 1=1

SESSION: abc@LOCAL
Timestamp: 10:22:49.057
Select owner, type_name, typecode FROM SYS.ALL_TYPES where owner is not null

SESSION: abc@LOCAL
Timestamp: 10:22:50.221
Select t.*, user owner from sys.user_all_tables t where 1=1
and ((iot_type is null) or (iot_type <> ‘IOT_MAPPING’))
and (( table_name = ‘WF_WORK_ITEM’) or
(table_name like ‘SYS_IOT_OVER%’ and iot_name = ‘WF_WORK_ITEM’))

SESSION: abc@LOCAL
Timestamp: 10:22:50.407
Select table_name, column_name, data_type, data_type_mod, data_type_owner,
decode(data_type, ‘CHAR’, char_length,
‘VARCHAR’, char_length,
‘VARCHAR2’, char_length,
‘NCHAR’, char_length,
‘NVARCHAR’, char_length,
‘NVARCHAR2’, char_length,
data_length) data_length,
data_precision, data_scale, nullable, char_used
, user owner
FROM SYS.USER_TAB_COLUMNS
WHERE 1=1
and table_name =:tn
order by column_id
tn = ‘WF_WORK_ITEM’

SESSION: abc@LOCAL
Timestamp: 10:22:50.700
Select TABLE_NAME, COLUMN_NAME, DEFAULT_LENGTH, DATA_DEFAULT
FROM SYS.USER_TAB_COLUMNS WHERE 1=1
And default_length is not null
and TABLE_NAME = ‘WF_WORK_ITEM’

SESSION: abc@LOCAL
Timestamp: 10:22:50.817
Select * from sys.user_refs where 1=1
and TABLE_NAME = ‘WF_WORK_ITEM’

SESSION: abc@LOCAL
Timestamp: 10:22:50.919
Select c.TABLE_NAME, l.LOG_GROUP_NAME, c.COLUMN_NAME, l.ALWAYS
from sys.user_log_groups l, sys.user_log_group_columns c
where 1=1
and l.LOG_GROUP_NAME = c.LOG_GROUP_NAME
and l.TABLE_NAME = c.TABLE_NAME
and c.table_name = ‘WF_WORK_ITEM’
order by c.TABLE_NAME, l.LOG_GROUP_NAME, c.POSITION

SESSION: abc@LOCAL
Timestamp: 10:22:51.096
Select * from SYS.ALL_INDEXES i where table_owner = :own
and index_type <> ‘LOB’
and table_name = ‘WF_WORK_ITEM’
own = ‘QALOCAL’

SESSION: abc@LOCAL
Timestamp: 10:22:51.427
Select t.*
from SYS.ALL_TRIGGERS t
where t.owner = :own
and t.table_name = ‘WF_WORK_ITEM’
own = ‘QALOCAL’

SESSION: abc@LOCAL
Timestamp: 10:22:51.561
Select table_name, column_name, s.segment_name,
chunk, pctversion, cache, logging, in_row,
S.tablespace_name, initial_extent, next_extent, min_extents,
pct_increase, max_extents, freelists, freelist_groups, buffer_pool
, Freepools, retention
from sys.user_segments s, sys.user_lobs L
where 1=1
AND L.SEGMENT_NAME = s.SEGMENT_NAME
AND S.SEGMENT_TYPE = ‘LOBSEGMENT’
and table_name = ‘WF_WORK_ITEM’
Runtime error occurred: 918 (ORA-00918: column ambiguously defined)

Thanks & Regards
Sami

Guest · June 9, 2011, 1:15am

Kindly unsubscribe me from this group.

Thanks & Regards
Shanmuga Karthik S

----- Original Message -----

JohnDorlon · June 9, 2011, 7:56am

If you uncheck ‘storage’ or ‘lob storage’ in the script
options, it should skip that query.

But as others have mentioned, upgrading your Toad is the best bet.

NormTeamT · June 10, 2011, 3:08am

Morning,

On 09/06/11 06:59, Shanmuga Karthik S wrote:

Kindly unsubscribe me from this group.
Thanks & Regards
Shanmuga Karthik S

You need to send an email to this address
mailto:toad-unsubscribe@yahoogroups.com?subject=unsubscribe from your
registered email address.

There's a link on the bottom of your email!

Cheers,
Norm.

--
Cheers,
Norm. [TeamT]

ORA-00918: column ambiguously defined

SESSION: abc@LOCAL Timestamp: 10:22:48.599 Select name from sys.user_snapshots where 1=1

SESSION: abc@LOCAL Timestamp: 10:22:49.057 Select owner, type_name, typecode FROM SYS.ALL_TYPES where owner is not null

SESSION: abc@LOCAL Timestamp: 10:22:50.221 Select t.*, user owner from sys.user_all_tables t where 1=1 and ((iot_type is null) or (iot_type <> ‘IOT_MAPPING’)) and (( table_name = ‘WF_WORK_ITEM’) or (table_name like ‘SYS_IOT_OVER%’ and iot_name = ‘WF_WORK_ITEM’))

SESSION: abc@LOCAL Timestamp: 10:22:50.700 Select TABLE_NAME, COLUMN_NAME, DEFAULT_LENGTH, DATA_DEFAULT FROM SYS.USER_TAB_COLUMNS WHERE 1=1 And default_length is not null and TABLE_NAME = ‘WF_WORK_ITEM’

SESSION: abc@LOCAL Timestamp: 10:22:50.817 Select * from sys.user_refs where 1=1 and TABLE_NAME = ‘WF_WORK_ITEM’

SESSION: abc@LOCAL Timestamp: 10:22:51.096 Select * from SYS.ALL_INDEXES i where table_owner = :own and index_type <> ‘LOB’ and table_name = ‘WF_WORK_ITEM’ own = ‘QALOCAL’

SESSION: abc@LOCAL Timestamp: 10:22:51.427 Select t.* from SYS.ALL_TRIGGERS t where t.owner = :own and t.table_name = ‘WF_WORK_ITEM’ own = ‘QALOCAL’

SESSION: abc@LOCAL
Timestamp: 10:22:48.599
Select name
from sys.user_snapshots
where 1=1

SESSION: abc@LOCAL
Timestamp: 10:22:49.057
Select owner, type_name, typecode FROM SYS.ALL_TYPES where owner is not null

SESSION: abc@LOCAL
Timestamp: 10:22:50.221
Select t.*, user owner from sys.user_all_tables t where 1=1
and ((iot_type is null) or (iot_type <> ‘IOT_MAPPING’))
and (( table_name = ‘WF_WORK_ITEM’) or
(table_name like ‘SYS_IOT_OVER%’ and iot_name = ‘WF_WORK_ITEM’))

SESSION: abc@LOCAL
Timestamp: 10:22:50.700
Select TABLE_NAME, COLUMN_NAME, DEFAULT_LENGTH, DATA_DEFAULT
FROM SYS.USER_TAB_COLUMNS WHERE 1=1
And default_length is not null
and TABLE_NAME = ‘WF_WORK_ITEM’

SESSION: abc@LOCAL
Timestamp: 10:22:50.817
Select * from sys.user_refs where 1=1
and TABLE_NAME = ‘WF_WORK_ITEM’

SESSION: abc@LOCAL
Timestamp: 10:22:51.096
Select * from SYS.ALL_INDEXES i where table_owner = :own
and index_type <> ‘LOB’
and table_name = ‘WF_WORK_ITEM’
own = ‘QALOCAL’

SESSION: abc@LOCAL
Timestamp: 10:22:51.427
Select t.*
from SYS.ALL_TRIGGERS t
where t.owner = :own
and t.table_name = ‘WF_WORK_ITEM’
own = ‘QALOCAL’