Interesting 8i "mixed case" object name...

Recently had a problem with an IDE for the Silverstream product. During it's scanning of the user database, it detected a duplicate table name where the two table names were identical but in different cases. Since we all know Oracle names are not case sensitive, we got to playing...

CREATE TABLE XXX."my_table"
(

my_col VARCHAR2(40)
);

create table XXX.MY_TABLE
(

my_col VARCHAR2(40)
);

This creates two different tables. You can access them separately, but need to use quote syntax for the "lowercase" one.

select * from XXX."my_table"
select * from XXX."MY_TABLE"
select * from XXX.MY_TABLE -- defaults to the uppercase version
select * from all_objects o   where upper(o.object_name) like 'MY%'

Silverstream apparently uses the all_objects (or similar) view to derive it's DB information, since it hurled when it hit the dual entries there. In this view, there are no quotes, just the two names in different cases.

We've fixed the problem, but are curious on how/why Oracle allows this. Ideas?

Tnx
Gary Received on Tue Apr 19 2005 - 16:35:23 CDT