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 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.
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
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.
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) Boolean literals are predefined values TRUE, FALSE, and NULL. NULL stands for a missing, unknown, or inapplicable value. Boolean literals are not strings.
4) Boolean literals are predefined values TRUE, FALSE, and NULL. NULL stands for a missing, unknown, or inapplicable value. Boolean literals are not strings.

PL/SQL Delimiters

A delimiter is a simple or compound symbol that has a special meaning to PL/SQL. For example, you use delimiters to represent arithmetic operations such as addition and subtraction. Simple symbols consist of one character and compound symbols consist of two characters.
For example:
TO_DATE('01-OCT-1986', 'DD-MON-YYYY')
TO_TIMESTAMP_TZ('01-OCT-1986 00:00:00 -6','DD-MON-YYYY HH24:MI:SS TZH')

Both expressions return October 1, 1986, with zero hours, zero minutes, and zero seconds; the first in the DATE datatype, and the second in the TIMESTAMP WITH TIME ZONE datatype. The second expression also includes time zone information; the -6 represents the number of hours difference from GMT (UCT). Unlike identifiers, string literals in PL/SQL are case-sensitive. As you would probably expect, the following two literals are different.
'Susan'
'susan'

So the following condition evaluates to FALSE:
IF 'Susan' = 'susan'

NULLs in PL/SQL

The absence of a value is represented in the Oracle database 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

Assigning a zero-length string to a VARCHAR2(n) variable in PL/SQL also yields a NULL result:
DECLARE
  str VARCHAR2(1) := '';
BEGIN
  IF str IS NULL -- will be TRUE

This behavior is consistent with the database's treatment of VARCHAR2 table columns.

CHAR Data

Let us look at CHAR data. If you create a CHAR(n) variable in PL/SQL and assign a zero-length string to it, the database blank-pads the empty variable with space characters, making it not null:
DECLARE
  flag CHAR(2) := ''; -- try to assign zero-length string to CHAR(2)
BEGIN
  IF flag = ' ' ... -- will be TRUE
  IF flag IS NULL ... -- will be FALSE

PL/SQL is the only place you will see such behavior. In the database, when you insert a zero-length string into a CHAR(n) table column, the database does not blank-pad the contents of the column, but leaves it NULL instead. These examples illustrate Oracle's partial adherence to the 92 and 99 versions of the ANSI SQL standard, which mandates a difference between a zero-length string and a NULL string.
Oracle admits this difference, and says they may fully adopt the standard in the future. They have been issuing that warning for about 15 years, though, and it has not happened yet. While NULL tends to behave as if its default datatype is VARCHAR2, the database will try to implicitly cast NULL to whatever type is needed for the current operation. Occasionally, you may need to make the cast explicit, using syntax such as TO_NUMBER(NULL) or CAST(NULL AS NUMBER).

Oracle Database PL/SQL Programming

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.
Oracle PL/SQL Best Practices
SEMrush Software