Re: Glaring Oracle 9i Bug

jcaddel_at_yahoo.com (Jeff Caddel) wrote in message news:<b1704b42.0302261449.67669c2_at_posting.google.com>...
> This does not work:
> create table foo(id varchar(5), ID varchar(5));
>
> But this does:
> create table foo("id" varchar(5), "ID" varchar(5));
>
> The first one produces an error
> ERROR at line 1:
> ORA-00957: duplicate column name
>
> The second one creates the table and "desc foo" yields:
> Name Null? Type
> ----------------- -------- -----------------
> id VARCHAR2(5)
> ID VARCHAR2(5)
>
> This does not seem right at all. After creating this table I can't
> select from or insert into the lowercase id column. Am I missing
> something here? I'm doing this using Oracle 9i on Windows 2000 using
> sqlplus at the command line.

This is not a bug but expected behavior.. and a good one too.

When you create objects in Oracle e.g. tables, procedures, columns etc.. Oracle treats the name as case insensitive so id and ID are treated as same thus resulting in duplicate name error. If this was not implemented... you could potentially have hundreds of objects with same name and mixed case... A programming and administration nightmare.

When you provide object name as a literal inside "", Oracle happily complies and preserves the case. But anytime when you want to refer to this object... you have to provide the proper case inside "". If you create a column as "id", then anytime you refer to this column... it has to be as "id" or Oracle will complain. You would have to do it like, insert into foo ("id", "ID") values ('a', 'b');

More importantly, you will seldom (probably never) see objects created with mixed case inside "" in commercial applications...definitely not with same name differentiated *ONLY* by case. So unless you are just trying to learn about Oracle intricacies... I would suggest to stay clear from this practice.

Regards
/Rauf Sarwar Received on Thu Feb 27 2003 - 05:25:11 CET