Usage of Mixed Case Database Object Names Is Dangerous

Some versions back, Oracle would not allow to create database object names with mixed cases,
even if we tried to create them, we could not.In newer versions of Oracle we can create tables, columns, indexes etc using mixed case or lower case, when the names are put inside double quotes.

For example:-

 CREATE TABLE "Customer" ( "CustomerID" number(10) ... );
CREATE INDEX "IDX_Customer_CustomerID" on "Customer"("CustomerID");

We created table named “Customer” with a column “CustomerID” and the index is named “IDX_Customer_CustomerID”. In the above example we can see that mixed case or lower case is supported and the table, column and index are created in the database. When these names are used, we have to reference them everywhere using the lower case letters. The following statement would be invalid.

Invalid SQL1

 SELECT * FROM Customer

 

                                    While the below statement is valid.

Valid SQL1

 SELECT * FROM "Customer"

 

We can even create a table such as “CusTomer” and it would be valid.

Customer table with different case

 CREATE TABLE "CusTomer" (

"CustomerID" number(10)

...

);

With the large amount of work involved in matching the case for the name of the database object, every time a DML/DDL statement is used against a database object, the confusion it creates when duplicate database objects are allowed to be created (“Customer” and “CusTomer” are valid names).

We can see that its better to avoid using database objects with mixed case names.