PL/SQL Datatypes  «Prev  Next»

Lesson 4 What are identifiers and literals?
Objective Place Identifiers and use Literals within a PL/SQL Block

Identifiers and Literals used within a PL/SQL Block

In PL/SQL, identifiers are used as variable names. The identifiers must follow the following naming conventions:
  1. An identifier must be less than or equal to 30 characters in length.
  2. An identifier must begin with a letter, and can be followed by any combinations of letters, numbers, and the characters $, _, and #.
  3. An identifier cannot be a reserved word unless it is enclosed within double quotes.
  4. Case does not matter for the identifier unless the identifier is enclosed in double quotes.

Scope of an Identifier

The scope of an identifier is that section of a program unit (block, subprogram, package) from which the identifier is referenced. Identifiers declared within a block are considered local to that block and global to all its nested blocks.
Global identifiers can be redeclared within a nested block, in which case the local declaration prevails. The nested block cannot reference the global identifier unless a qualified name is used. The qualifier can be the name of an enclosing subprogram or the label of an enclosing block.

Oracle PL/SQL Literals

A literal is an explicit numeric, character, string, or boolean value not represented by an identifier. Let us try to understand each type of literal with the help of the following SlideShow.


  1. Two kinds of numeric literals can be used in arithmetic expressions: 1) integer and 2) real. Numeric literals cannot contain dollar signs or commas, but they can be written using scientific notation.
  2. Character Literal is an individual character enclosed within single quotes. It cannot be used within arithmetic expressions.
  3. A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of characters enclosed by single quotes.
  4. Boo>lean literals are predefined values TRUE, FALSE, and NULL. NULL stands for a missing, unknown, or inapplicable value. Boolean literals are not strings.
PL SQL Identifiers Literals
A literal is a value that is not represented by an identifier; it is simply a value. Below is a sequence of literals one can see in a PL/SQL program:
Number
415, 21.6, 3.141592654f, 7D, NULL
String
'This is my sentence', '01-OCT-1986', q'!hello!', NULL
Time interval
INTERVAL '25-6' YEAR TO MONTH, INTERVAL '-18' MONTH, NULL
Boolean
TRUE, FALSE, NULL

The trailing "f" in number literal 3.14159f designates a 32-bit floating point number as defined by the IEEE 754 standard, which Oracle partially supports beginning with Oracle Database 10g Release 1. Similarly, 7D is the number 7 as represented in a 64- bit float.
The string q'!hello!' bears some explanation. The ! is a user-defined delimiter, also introduced in Oracle Database 10g; the leading q and the surrounding single quotes tell the compiler that the ! is the delimiter, and the string represented is simply the word hello.
The INTERVAL datatype allows you to manage amounts of time between dates or timestamps. The first example above represents
"25 years and 6 months after";

the second represents "18 months before."
Even though the database allows you to specify intervals using a literal format, you cannot do so with DATE datatypes; notice that '01-OCT-1986' is listed as a string rather than as an Oracle DATE. Yes, PL/SQL or SQL can implicitly convert '01-OCT-1986' to and from Oracle's internal date format, but you will normally use built-in functions to perform explicit conversions.
PL/SQL is case sensitive within string literals.
In the next lesson, adding comments to a PL/SQL block will be discussed.