Sunday, November 25, 2012

Case Sensitivity while declaring a database object in Oracle

Database objects are case-sensitive & will be treated as though they are typed in upper case.
This is generally true - but there is exception and its depends on whether you use double quotes " or not.

  • If a name is not enclosed in a double quotes, then it will be treated as uppercase regardless of how is created or referenced.
  • If a name is enclosed in a double quotes, then it is case sensitive and must always will be referenced with case sensitivity and double quotes
CREATE TABLE latte(Name Varchar2(20));

The database will automatically convert latte into LATTE.So you can reference table as 

Select * from latte;
Select * from LATTE;

Then every future reference will require double quotes and case sensitive reference to this.

Select * from "latte"; will work.
Select * from LATTE; not work. 

Not either this one
Select * from latte.

There is more !!! By using double quotes, you can also include special character that are otherwise not allowed -- space.
This will work
CREATE TABLE "java latte"(name varchar2(20));

1 comment: