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

Advertisements

One thought on “Did You Know #8 – Identifiers

  1. Another downside is that you will ALWAYS have to double quote the identifier. Of course, coming from a Sybase background, the configuration of our servers allowed object names such as Tab1 TAb1 TAB1 and so on. This lead to a lot of standards for ALL_UPPER_CASE names with underscores. Years later with more .Net development, there was a push to CamelCaseNames with no underscores.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s