[Chapter 2] 2.2 Identifiers

2.2 Identifiers

An identifier is a name for a PL/SQL object, including any of the following:

Constant
Variable
Exception
Procedure
Function
Package
Record
PL/SQL table
Cursor
Reserved word

Properties of an identifier are summarized below:

Up to 30 characters in length
Must start with a letter
Can include $ (dollar sign), _ (underscore), and # (pound sign)
Cannot contain spaces

Remember that PL/SQL is not case-sensitive, so if the only difference between two identifiers is the case of one or more letters, PL/SQL treats those two identifiers as the same. For example, the following identifiers are all considered by PL/SQL to be the same, because the characters in the name are the same; the only difference is their case:

lots_of_$MONEY$
LOTS_of_$MONEY$
Lots_of_$Money$

The following strings are valid identifier names:

lots_of_$MONEY$	FirstName
company_id#	address_line1
primary_acct_responsibility	address_line2
First_Name	S123456

The following identifiers are all illegal in PL/SQL:

1st_year	-- Starts with numeral
procedure-name	-- Contains invalid character "-"
minimum_%_due	-- Contains invalid character "%"
maximum_value_exploded_for_detail	-- Name is too long
company ID	-- Cannot have embedded spaces in name

Identifiers are the handles for objects in your program. Be sure to name your objects carefully, so the names describe the objects and their uses. Avoid identifier names like X1 and temp; they are too ambiguous to mean anything to you or anyone else reading your code.

2.2.1 Reserved Words

Of course, you don't get to name all the identifiers in your programs. Many elements of your program have been named by PL/SQL itself. These are the reserved words in the language. An identifier is a reserved word if it has a special meaning in PL/SQL and therefore should not -- and, in most cases, cannot -- be redefined by programmers for their own use.

One very important reserved word is END. It is used to terminate programs, IF statements, and loops. If you try to declare a variable named "end", as I do below, then you will get the subsequent compile error:

DECLARE
   end VARCHAR2(10) := 'blip';
BEGIN
   DBMS_OUTPUT.PUT_LINE (end);
END;
/
PLS-00103: Encountered the symbol "END" when expecting one of the following:

The appearance of the word "end" in the declaration section signals to PL/SQL the premature termination of that anonymous block.

PL/SQL tries to be as accommodating as possible when you do redefine reserved words in your program. It makes its best effort to interpret and compile your code. You would be much better off, however, if you never redefined a reserved word for your own use. Even if you do get away with it, the resulting code will be confusing. And a later version of the PL/SQL compiler might decide that your use of that keyword is, after all, unacceptable.

In any case, finding a valid name for your identifier should be the least of your problems. There are thousands and thousands of permutations of the legal characters.

You can find a full list of all PL/SQL reserved words in an appendix of Oracle Corporation's PL/SQL User's Guide and Reference .

2.2.2 Whitespace and Keywords

Identifiers must be separated by at least one space or by a delimiter, but you can format your text by adding additional spaces, line breaks (carriage returns), and tabs wherever you can put a space, without changing the meaning of your entry.

The two statements shown below are therefore equivalent:

IF too_many_orders
THEN
   warn_user;
ELSIF no_orders_entered
THEN
   prompt_for_orders;
END IF;

IF too_many_orders THEN warn_user;
ELSIF no_orders_entered THEN prompt_for_orders;
END IF;

You may not, however, place a space or carriage return or tab within a lexical unit, such as a compound delimiter like the "not equals" symbol (!= ). The following statement raises a compile error:

IF max_salary ! = min_salary THEN

because there is a space between the ! and the = .

See Chapter 3, Effective Coding Style , for more information on how you can use whitespace to format your code for improved readability.