Did you know this? Object Names in Oracle

Hard and fast rule in any environment, no two objects can have the same name in the same session or schema. But we also know about procedure overloading where we have same procedure names but different signatures.

The point I wanted to make here was that in Oracle for the below example

employees
emPlOyeeS
Employees
EMPLOYEES

an object with the above name would be treated the same in a particular schema, Oracle reads all objects converted to uppercase and in this example it would be EMPLOYEE. As we know only one object with the name EMPLOYEE can exist in one particular schema. But there is a clause to it, by enclosing object names in double quotation marks we can give the following names to different object names in the same namespace

employees
"emPlOyeeS"
"Employees"
"EMPLOYEES"

You would see the below snippet from Oracle documentation for further knowledge on naming. Source :http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements008.htm

The following schema objects share one namespace:

• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone procedures
• Stand-alone stored functions
• Packages
• Materialized views
• User-defined types

Each of the following schema objects has its own namespace:

• Indexes
• Constraints
• Clusters
• Database triggers
• Private database links
• Dimensions

Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.

Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.

Each of the following nonschema objects also has its own namespace:

• User roles
• Public synonyms
• Public database links
• Tablespaces
• Profiles
• Parameter files (PFILEs) and server parameter files (SPFILEs)

Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.