Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 2.2 IdentifiersChapter 2
PL/SQL Language Fundamentals
Next: 2.4 The Semicolon Delimiter
 

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:

LiteralActual 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:

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.


Previous: 2.2 IdentifiersOracle PL/SQL Programming, 2nd EditionNext: 2.4 The Semicolon Delimiter
2.2 IdentifiersBook Index2.4 The Semicolon Delimiter

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.