Did You Know #8 – Identifiers

Database Identifiers are basically names of objects in the database, like tables, indexes, etc.
There are restrictions about identifiers in Oracle, they can be up to 30 characters, must begin with a letter and can’t have special characters (dollar sign ‘$’, underscore ‘_’ and hash sign ‘#’ are allowed). Also, the identifier is converted to upper-case before it is saved to the data dictionary.
If you want to overcome these restrictions (except the length), you can use double quotation marks (“) to enclose the identifier. Quoted identifiers can start with any character, can have any special character and can even be lower case or reserved words. However, this is not recommended (for example, in some old versions I ran into cases where “exp” couldn’t export tables with lower-case name).
Examples:

SQL> create table xxx.tab (a number);
create table xxx.tab (a number)
*
ERROR at line 1:
ORA-01918: user 'XXX' does not exist
SQL> create table "XXX.TAB" (a number);
Table created.
SQL> create table "lower" (a number);
Table created.
SQL> create table 1table (a number);
create table 1table (a number)
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> create table "1TABLE" (a number);
Table created.
SQL> select table_name from tabs;
TABLE_NAME
-----------------------------------------------------
XXX.TAB
lower
1TABLE