3.3 Identifiers

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

• Constant

• Scalar variable

• Composite variable (record or collection)

• Exception

• Procedure

• Function

• Package

• Type

• Cursor

• Reserved word

• Label

Default properties of PL/SQL identifiers 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

If the only difference between two identifiers is the case of one or more letters, PL/SQL normally treats those two identifiers as the same.^[1] For example, the following identifiers are all considered by PL/SQL to be the same:

^[1] The compiler accomplishes this by internally converting program text into uppercase during an early compiler phase.

lots_of_$MONEY$

LOTS_of_$MONEY$

Lots_of_$Money$

The following strings are valid names of identifiers:

company_id#

primary_acct_responsibility

First_Name

FirstName

address_line1

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 to anyone else reading your code.

Although rarely done in practice, you can actually break some of these rules by surrounding identifiers with double quotation marks. I don't recommend programming like this, but you may one day have to deal with some "clever" code such as:

SQL> DECLARE

2 "pi" CONSTANT NUMBER := 3.141592654;

3 "PI" CONSTANT NUMBER := 3.14159265358979323846;

4 "2 pi" CONSTANT NUMBER := 2 * "pi";

5 BEGIN

6 DBMS_OUTPUT.PUT_LINE('pi: ' || "pi");

7 DBMS_OUTPUT.PUT_LINE('PI: ' || pi);

8 DBMS_OUTPUT.PUT_LINE('2 pi: ' || "2 pi");

9* END;

10 /

pi: 3.141592654

PI: 3.14159265358979323846

2 pi: 6.283185308

Notice that line 7 refers to pi without quotation marks. Because the compiler accomplishes its case-independence by defaulting identifiers and keywords to uppercase, the variable that line 7 refers to is the one declared on line 3 as "PI".

On rare occasions, you may need to use the double-quote trick in SQL statements to refer to database objects that exist with mixed-case names. I've seen this happen when a programmer used Microsoft Access to create the Oracle tables.

3.3.1 Reserved Words

Of course, you don't get to name all the identifiers in your programs. The PL/SQL language recognizes certain identifiers (such as BEGIN, IF, and THEN) as having special meaning.

There are two kinds of built-in identifiers that PL/SQL provides:

• Language keywords

• Identifiers from the STANDARD package

In both cases you should not—and, in many cases, cannot—redefine the identifier for your program's own use.