PL/SQL Datatypes  «Prev

PL/SQL Identifiers and Literals


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.

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

Oracle Database PL/SQL Programming


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:
Assigning a zero-length string to a VARCHAR2(n) variable in PL/SQL also yields a NULL result:
  str VARCHAR2(1) := '';
  IF str IS NULL -- will be TRUE

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


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:

  flag CHAR(2) := ''; -- try to assign zero-length string to CHAR(2)
  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 PL/SQL Best Practices
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) Boolean literals are predefined values TRUE, FALSE, and NULL. NULL stands for a missing, unknown, or inapplicable value. Boolean literals are not strings.