Qualifying identifier names with module names

When necessary, PL/SQL offers many ways to qualify an identifier so that a reference to the identifier can be resolved. Using packages, for example, you can create variables with "global" scope. Suppose that I create a package called company_pkg and declare a variable named last_company_id in that package's specification, as follows:

PACKAGE company_pkgIS last_company_id NUMBER; ...END company_pkg;

Then, when I reference that variable outside of the package, I must preface the identifier name with the package name:

IF new_company_id = company_pkg.last_company_id THEN

Because a variable declared in a package specification is global in your session, the last_company_id variable can be referenced in any program, but it is not visible unless it is qualified.

I can also qualify the name of an identifier with the module in which it is defined:

PROCEDURE calc_totals IS salary NUMBER;BEGIN ... DECLARE salary NUMBER; BEGIN salary := calc_totals.salary; END; ...END;

The first declaration of salary creates an identifier whose scope is the entire procedure. In the nested block, however, I declare another identifier with the same name. So when I reference the variable "salary" inside the inner block, it will always be resolved first against the declaration in the inner block, where that variable is visible without any qualification. If I wish to make reference to the procedure-wide salary variable inside the inner block, I must qualify that variable name with the name of the procedure (cal_totals.salary).

PL/SQL goes to a lot of trouble and has established many rules for determining how to resolve such naming conflicts. While it is good to be aware of such issues, you would be much better off never having to rely on these guidelines. Use unique names for your identifiers in different nested blocks so that you can avoid naming conflicts altogether.

3.2 The PL/SQL Character Set

A PL/SQL program consists of a sequence of statements, each made up of one or more lines of text. The precise characters available to you will depend on what database character set you're using. For example, Table 3-2 illustrates the available characters in the US7ASCII character set.

Table 3-2. Characters available to PL/SQL in the US7ASCII character set
Type	Characters
Letters	A-Z, a-z
Digits	0-9
Symbols	~ ! @ # $ % & * ( ) _ - + = | [ ] { } : ; " ' < > , . ? / ^
Whitespace	Tab, space, newline, carriage return

Every keyword in PL/SQL is made from various combinations of characters in this character set. Now you just have to figure out how to put them all together!

By default, PL/SQL is a case-insensitive language. That is, uppercase letters are treated the same way as lowercase letters except when characters are surrounded by single quotes, which makes them a literal string.

A number of these characters—both singly and in combination with other characters—have a special significance in PL/SQL.Table 3-3 lists these special symbols.

Table 3-3. Simple and compound symbols in PL/SQL
Symbol	Description
;	Semicolon: terminates declarations and statements
%	Percent sign: attribute indicator (cursor attributes like %ISOPEN and indirect declaration attributes like %ROWTYPE); also used as multibyte wildcard symbol with the LIKE condition
_	Single underscore: single-character wildcard symbol in LIKE condition
@	At- sign: remote location indicator
:	Colon: host variable indicator, such as :block.item in Oracle Forms
**	Double asterisk: exponentiation operator
< > or != or ^= or ~=	Ways to denote the "not equal" relational operator
||	Double vertical bar: concatenation operator
<< and >>	Label delimiters
<= and >=	Less than or equal, greater than or equal r elational operators
:=	Assignment operator
=>	Association operator for positional notation
..	Double dot: range operator
--	Double dash: single-line comment indicator
/* and */	Beginning and ending multiline comment block delimiters

Characters are grouped together into lexical units, also called atomics of the language because they are the smallest individual components. A lexical unit in PL/SQL is any of the following:

· Identifier

· Literal

· Delimiter

· Comment

These are described in the following sections.

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_responsibilityFirst_NameFirstNameaddress_line1S123456

The following identifiers are all illegal in PL/SQL:

1st_year -- Starts with numeralprocedure-name -- Contains invalid character "-"minimum_%_due -- Contains invalid character "%"maximum_value_exploded_for_detail -- Name is too longcompany 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.141592654PI: 3.141592653589793238462 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.