We can use identifiers to name PL/SQL program items and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms, and packages. Some examples of identifiers are given below:
a
x2
new#
debit_limit
firstName
ora$number
An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Other characters such as hyphens, slashes, and spaces are not allowed, as the following examples show:
me&you -- not allowed because of ampersand
credit-limit -- not allowed because of hyphen
in/out -- not allowed because of slash
user name -- not allowed because of space
You can use upper, lower, or mixed case to write identifiers. PL/SQL is not case sensitive except within string and character literals. So, if the only difference between identifiers is the case of corresponding letters, PL/SQL considers the identifiers to be the same, as the following example shows:
username -- same as USERNAME
SSN -- same as ssn
The size of an identifier cannot exceed 30 characters. But, every character, including dollar signs, underscores, and number signs, is significant. For example, PL/SQL considers the following identifiers to be different:
newpassword
new_password
Identifiers should be descriptive. So, avoid obscure names and use meaningful names.
Quoted Identifiers
For flexibility, PL/SQL lets you enclose identifiers within double quotes. Quoted identifiers are seldom needed, but occasionally they can be useful. They can contain any sequence of printable characters including spaces but excluding double quotes. Thus, the following identifiers are valid:
"X+Y"
"last name"
"on/off switch"
"employee(s)"
"*** header info ***"
The maximum size of a quoted identifier is 30 characters not counting the double quotes. Though allowed, using PL/SQL reserved words as quoted identifiers is a poor programming practice.
Some PL/SQL reserved words are not reserved by SQL. For example, you can use the PL/SQL reserved word TYPE in a CREATE TABLE statement to name a database column. But, if a SQL statement in your program refers to that column, you get a compilation error, as the following example shows:
SELECT acct, type, bal INTO ... -- causes compilation error
To prevent the error, enclose the uppercase column name in double quotes, as follows:
SELECT acct, "TYPE", bal INTO ...
The column name cannot appear in lower or mixed case (unless it was defined that way in the CREATE TABLE statement). For example, the following statement is invalid:
SELECT acct, "type", bal INTO ... -- causes compilation error
Alternatively, you can create a view that renames the troublesome column, then use the view instead of the base table in SQL statements.
Predefined Identifiers
Identifiers globally declared in package STANDARD, such as the exception INVALID_NUMBER, can be redeclared. However, redeclaring predefined identifiers is error prone because your local declaration overrides the global declaration.
