Oracle PL/SQL for DBAs

Identifiers

An identifier is a name for a PL/SQL object, such as a variable, program name, or reserved word. The 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 any “whitespace " characters

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:

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

NULLs

The absence of a value is represented in Oracle by the keyword NULL. As shown in the previous section, variables of almost all PL/SQL datatypes can exist in a null state (the exception to this rule is any associative array type, instances of which are never null). Although it can be challenging for a programmer to handle NULL variables properly regardless of their datatype, strings that are null require special consideration.

In Oracle SQL and PL/SQL, a null string is usually indistinguishable from a literal of zero characters, represented literally as '' (two consecutive single quotes with no characters between them). For example, the following expression will evaluate to TRUE in both SQL and PL/SQL:

    '' IS NULL

While NULL tends to behave as if its default datatype is VARCHAR2, Oracle will try to implicitly cast NULL to whatever type is needed for the current operation. Occasionally, you may ...