[Chapter 2] 2.3 Literals

2.3 Literals

A literal is a value which is not represented by an identifier; it is simply a value. A literal may be composed of one of the following types of data:

Number: 415, 21.6, or NULL
String: `This is my sentence' or `31-JAN-94' or NULL
Boolean: TRUE, FALSE, or NULL

Notice that there is no way to indicate a true date literal. The value `31-JAN-94' is a string literal (any sequence of characters enclosed by single quotes is a string literal). PL/SQL and SQL automatically convert such a string to a date for you (by calling TO_DATE), but a date has only an internal representation.

A string literal can be composed of zero or more characters from the PL/SQL character set. A literal of zero characters is represented as '' (two consecutive single quotes with no characters between them) and is defined as the NULL string. This literal has a datatype of CHAR (fixed-length string).

PL/SQL is case-sensitive within string literals. The following two literals are different:

'Steven'
'steven'

The following condition, for example, evaluates to FALSE:

IF 'Steven' = 'steven'

2.3.1 Embedding Single Quotes Inside a String

The trickiest part of working with string literals comes when you need to include a single quote inside a string literal (as part of the literal itself). Generally, the rule is that you write two single quotes next to each other inside a string if you want the literal to contain a single quote in that position. The following table shows the literal in one column and the resulting "internal" string in the second column:

Literal	Actual Value
'There''s no business like show business.'	There's no business like show business.
'"Hound of the Baskervilles"'	"Hound of the Baskervilles"
'NLS_LANGUAGE=''ENGLISH'''	NLS_LANGUAGE='ENGLISH'
''''	'
'''hello'''	'hello'
''''''	''

Here's a summary of how to embed single quotes in a literal:

To place a single quote inside the literal, put two single quotes together.
To place a single quote at the beginning or end of a literal, put three single quotes together.
To create a string literal consisting of one single quote, put four single quotes together.
To create a string literal consisting of two single quotes together, put six single quotes together.

Two single quotes together is not the same as a double quote character. A double quote character does not have any special significance inside a string literal. It is treated the same as a letter or number.

2.3.2 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, even though the fractional component is zero and the number is actually an integer. You can also use scientific notation to specify a numeric literal. Use the letter "e" (upper- or lowercase) to raise a number times 10 to the nth power. For example: 3.05E19, 12e-5.

2.3.3 Boolean Literals

Remember that the values TRUE and FALSE are not strings. They are Boolean literals with the literal meaning of TRUE or FALSE. You should never place single quotes around these values. The following code will fail to compile with the error listed below:

DECLARE
   enough_money BOOLEAN;
BEGIN
   IF enough_money = 'TRUE'
   THEN
      ...

PLS-00306: wrong number or types of arguments in call to '='

Instead, you should reference the literal directly:

DECLARE
   enough_money BOOLEAN;
BEGIN
   IF enough_money = TRUE
   THEN
      ...

Better yet, leave out the literal and just let the Boolean variable speak for itself in the conditional clause of the IF statement:

DECLARE
   enough_money BOOLEAN;
BEGIN
   IF enough_money
   THEN
      ...

If you work with Oracle Forms, you may notice that some of the GET_ built-ins, such as GET_ITEM_PROPERTY, sometimes return a value of TRUE or FALSE. For example:

GET_ITEM_PROPERTY ('block.item', DISPLAYED)

returns the character string FALSE if the DISPLAYED property is set to "Off" for the specified item. Do not confuse this value with the Boolean literal. The easiest way to keep this all straight is to remember that the GET_ built-ins always return a character string.


2.2 Identifiers		2.4 The Semicolon Delimiter