Section 3.4.  Literals

3.4. Literals A literal is a value that is not represented by an identifier; it is simply a value. Here is a smattering of literals you could see in a PL/SQL program: Number 415, 21.6, 3.141592654f, 7D, NULL String 'This is my sentence', '01-OCT-2006', 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 notion of a time interval is yet another feature introduced in Oracle Database 10g; the first example above represents "25 years and six months after"; the second represents "18 months before." Even though Oracle allows you to input time intervals using a literal format, you cannot do so with DATE datatypes; notice that '01-OCT-2006' is listed as a string rather than as an Oracle DATE. Yes, PL/SQL or SQL can implicitly convert '01-OCT-2006' to and from Oracle's internal date format,[*] but you will normally use built-in functions to perform explicit conversions. For example: [*] As long as the database or session has its NLS_DATE_FORMAT parameter set to DD-MON-YYYY. TO_DATE('01-OCT-2006', 'DD-MON-YYYY') TO_TIMESTAMP_TZ('01-OCT-2006 00:00:00 -6','DD-MON-YYYY HH24:MI:SS TZH') Both expressions return February 1, 2006, 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. 'Steven' 'steven' So the following condition evaluates to FALSE: IF 'Steven' = 'steven' 3.4.1. 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 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 Oracle's treatment of VARCHAR2 table columns. Let's look at CHAR data, thoughit's a little quirky. If you create a CHAR(n) variable in PL/SQL and assign a zero-length string to it, Oracle 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 Strangely, 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, Oracle 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've been issuing that warning for about 10 years, though, and it hasn't happened yet. 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 need to make the cast explicit, using syntax such as TO_NUMBER(NULL) or CAST(NULL AS NUMBER). 3.4.2. Embedding Single Quotes Inside a Literal String An unavoidably ugly aspect of working with string literals occurs when you need to put the delimiter itself inside the string. Until Oracle Database 10g was released, you would write two single quotes next to each other if you wanted the string to contain a single quote in that position. Some examples: Literal (default delimiter) Actual value 'There''s no business like show business.' '"Hound of the Baskervilles"' '''' '''hello''' ''''''

There's no business like show business. "Hound of the Baskervilles" ' 'hello' ''

The examples show, for instance, that it takes six single quotes to designate a literal containing two consecutive single quotes. In an attempt to simplify this type of construct, Oracle Database 10g Release 1 introduced user-defined delimiters
. Start the literal with "q" to mark your delimiter, and surround your delimited expression with single quotes. The table below shows this feature in action:

Literal (delimiters highlighted)	Actual value
q'(There's no business like show business.)' q'{"Hound of the Baskervilles"}' q'[']' q'!'hello'!' q'|''|'

There's no business like show business.

"Hound of the Baskervilles"

'

'hello'

''

As the examples show, you can use plain delimiters such as ! or |, or you can use "mated" delimiters such as left and right parentheses, curly braces, and square brackets.

One final note: as you would expect, a double quote character does not have any special significance inside a string literal. It is treated the same as a letter or number.

3.4.3. Numeric Literals

Numeric literals can be integers
or real numbers


(a number that contains a fractional component). Note that PL/SQL considers the number 154.00 to be a real number of type NUMBER, even though the fractional component is zero, and the number is actually an integer. Internally, integers and reals have a different representation, and there is some small overhead involved in converting between the two.

You can also use scientific notation to specify a numeric
literal. Use the letter E (upper- or lowercase) to multiply a number by 10 to the nth power (e.g., 3.05E19, 12e-5).

Beginning in Oracle Database 10g Release 1, a real can be either an Oracle NUMBER type or an IEEE 754 standard floating-point
type. Floating-point literals are either BINARY (32-bit) (designated with a trailing F) or BINARY DOUBLE (64-bit) (designated with a D).

In certain expressions, you may use the following named constants, as prescribed by the IEEE standard:

Description	Binary float (32 bit)	Binary double (64 bit)
"Not a number" (NaN); result of divide by 0 or invalid operation	Binary_FLOAT_NAN

Binary_DOUBLE_NAN

Positive infinity

Binary_FLOAT_INFINITY

Binary_DOUBLE_INFINITY

Maximum finite number that is less than the overflow threshold

Binary_FLOAT_MAX_NORMAL

Binary_DOUBLE_MAX_NORMAL

Smallest normal number; underflow threshold

Binary_FLOAT_MIN_NORMAL

Binary_DOUBLE_MIN_NORMAL

Maximum positive number that is less than the underflow threshold

Binary_FLOAT_MAX_SUBNORMAL

Binary_DOUBLE_MAX_SUBNORMAL

Absolute minimum number that can be represented

Binary_FLOAT_MIN_SUBNORMAL

Binary_DOUBLE_MIN_SUBNORMAL

3.4.4. Boolean Literals

PL/SQL provides two literals to represent Boolean values: TRUE and FALSE. These values are not strings; you should not put quotes around them. Use Boolean literals
to assign values to Boolean variables, as in:

    DECLARE
       enough_money BOOLEAN; -- Declare a Boolean variable
    BEGIN
       enough_money := FALSE; -- Assign it a value
    END;

You do not, on the other hand, need to refer to the literal value when checking the value of a Boolean expression. Instead, just let that expression speak for itself, as shown in the conditional clause of the following IF statement:

    DECLARE
       enough_money BOOLEAN;
    BEGIN
       IF enough_money
       THEN
          ...

A Boolean expression, variable, or constant may also evaluate to NULL, which is neither TRUE nor FALSE. For more information, see Chapter 4, particularly the sidebar "Three-Valued Logic."