Oracle PL/SQL Programming, 5th Edition

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-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.”

To specify literal values of dates and times, you can use the DATE or TIMESTAMP keyword:

DATE '1986-10-01'
TIMESTAMP '1986-10-01 00:00:00 -6:00'

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 ...