Using Literals

Previous

Next

A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. For example, 147 is a numeric literal, and FALSE is a Boolean literal.

Numeric Literals

Two kinds of numeric literals can be used in arithmetic expressions: integer and real. An integer literal is an optionally signed whole number without a decimal point, such as +6. A real literal is an optionally signed whole or fractional number with a decimal point, such as -3.14159. PL/SQL considers a number such as 25. to be real, even though it has an integral value.

Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Add an E (or e) after the base number, followed by an optionally signed integer, for example -9.5e-3. The E (or e) represents the base number ten and the following integer represents the exponent.

Example: Using Numeric Literals in PL/SQL shows some examples of numeric literals.

DECLARE  -- declare and assign variables
  number1 PLS_INTEGER := 32000;  -- numeric literal
  number2 NUMBER(8,3);
BEGIN
  number2 := 3.125346e3;  -- numeric literal
  number2 := -8300.00;  -- numeric literal
  number2 := -14;  -- numeric literal
END;
/

Character Literals

A character literal is an individual character enclosed by single quotation marks (apostrophes), such as '(' or '7'. Character literals include all the printable characters in the PL/SQL character set: letters, numbers, spaces, and special symbols.

PL/SQL is case-sensitive within character literals. For example, PL/SQL considers the character literals 'Z' and 'z' to be different. The character literals '0'...'9' are not equivalent to integer literals, but can be used in arithmetic expressions because they are implicitly convertible to integers.

Example: Using Character Literals in PL/SQL shows some examples of character literals.

DECLARE  -- declare and assign variables
  char1   VARCHAR2(1) := 'x'; -- character literal
  char2   VARCHAR2(1);
BEGIN
  char2 := '5'; -- character literal
END;
/

String Literals

A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotation marks, such as 'Hello, world!' and '$1,000,000'.

PL/SQL is case-sensitive within string literals. For example, PL/SQL considers the string literals 'baker' and 'Baker' to be different:

To represent an apostrophe within a string, you can use two single quotation marks (''), which is not the same as a quotation mark ("). You can also use the quote-delimiter mechanism, which enables you to specify q or Q followed by a single quotation mark and then another character to be used as the quotation mark delimiter. See "Using Character Literals in SQL Statements".

Example: Using String Literals in PL/SQL shows some examples of string literals.

DECLARE  -- declare and assign variables
  string1   VARCHAR2(1000);
  string2   VARCHAR2(32767);
BEGIN
  string1 := '555-111-2323';
 -- the following needs two single quotation marks to represent one in the string
  string2 := 'Here''s an example of two single quotation marks used in a string.';
END;
/

BOOLEAN Literals

BOOLEAN literals are the predefined values: TRUE, FALSE, and NULL. NULL is a missing, unknown, or inapplicable value. BOOLEAN literals are values, not strings.

Example: Using BOOLEAN Literals in PL/SQL shows some examples of BOOLEAN literals.

DECLARE  -- declare and assign variables
  finished      BOOLEAN := TRUE; -- BOOLEAN literal
  complete      BOOLEAN; -- BOOLEAN literal
  true_or_false BOOLEAN;
BEGIN
  finished := FALSE;  -- BOOLEAN literal set to FALSE
  complete := NULL; -- BOOLEAN literal with unknown value
  true_or_false := (3 = 4); -- BOOLEAN literal set to FALSE
  true_or_false := (3 < 4); -- BOOLEAN literal set to TRUE
END;
/

Date-time Literals

Date-time literals have various formats depending on the date-time datatype used, such as '14-SEP-05' or '14-SEP-05 09:24:04 AM'.

Example: Using Date-time Literals in PL/SQL shows some examples of date-time literals.

DECLARE  -- declare and assign variables
  date1   DATE := '11-AUG-2005'; -- DATE literal
  time1   TIMESTAMP;
  time2   TIMESTAMP WITH TIME ZONE;
BEGIN
  time1 := '11-AUG-2005 11:01:01 PM'; -- TIMESTAMP literal
  time2 := '11-AUG-2005 09:26:56.66 PM +02:00'; -- TIMESTAMP WITH TIME ZONE 
END;
/