Oracle Literals

Literals

The terms literal refers to a fixed data value. For example, 'DAVID', 'w3resource', and '405' are all character literals; 4567 is a numeric literal. Character literals are enclosed in single quotation marks so that Oracle can distinguish them from schema object names. Here we have discussed the syntactical and structural rules for writing the following elements of Oracle statements.

• Text Literals
• Numeric Literals
• Datetime Literals
• Interval Literals

Text Literals

The text specifies a text or character literal. Use this notation to specify values whenever 'text' or char appear in expressions, conditions, SQL functions, and SQL statements in other parts of this reference. Text, character, and string literals are always surrounded by single quotation marks. This reference uses the terms text literal and character literal interchangeably.

Syntax:

[ {N | n} ]
{ '[ c ]...'
| { Q | q } 'quote_delimiter c [ c ]... quote_delimiter'
}

Where:

• N or n specifies the literal using the national character set. Text entered using this notation is translated into the national character set by Oracle when used. In the top branch of the syntax:
• c is any member of the user's character set. A single quotation mark (') within the literal must be preceded by an escape character. To represent one single quotation mark within a literal, enter two single quotation marks.
• ' ' are two single quotation marks that begin and end text literals.
• Q or q indicates that the alternative quoting mechanism will be used. This mechanism allows a wide range of delimiters for the text string.
• The outermost ' ' are two single quotation marks that precede and follow, respectively, the opening and closing quote_delimiter.
• c is any member of the user's character set. You can include quotation marks (") in the text literal made up of c characters. You can also include the quote_delimiter, as long as it is not immediately followed by a single quotation mark.
• quote_delimiter is any single- or multibyte character except space, tab, and return. The quote_delimiter can be a single quotation mark. However, if the quote_delimiter appears in the text literal itself, ensure that it is not immediately followed by a single quotation mark.

Here are some valid text literals:

• 'w3resource'
• 'w3resource.com'
• 'David ' 's raincoat'
• '11-01-2015'
• N'nchar literal'

Here are some valid text literals using the alternative quoting mechanism:

• q'!name LIKE '%DBMS_%%'!'
• q'<'So,' she said, 'It's finished.'>'
• q'{SELECT * FROM employees WHERE last_name = 'Smith';}'
• nq'ï Ÿ1234 ï'
• q'"name like '['"'

Numeric Literals

Numeric literal notation is used to specify fixed and floating-point numbers. Floating-point numbers use '.' as a decimal separator. Both types of numbers may be preceded by '+' or '-' to indicate a positive or negative numbers. You can use the integer notation in expressions, conditions, SQL functions, and SQL statements. The examples of integer is as follows:

Syntax of integer:

[ + | - ] digit [ digit ]...

where digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.

An integer can store a maximum of 38 digits of precision.

Here are some valid integers:

8
+256

NUMBER and Floating-Point Literals

Syntax of number follows:

[ + | - ]
{ digit [ digit ]... [ . ] [ digit [ digit ]... ]
| . digit [ digit ]...
}
[ e [ + | - ] digit [ digit ]... ]
[ f | d ]

where

• + or - indicates a positive or negative value. If you omit the sign, then a positive value is the default.
• digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.
• e or E indicates that the number is specified in scientific notation. The digits after the E specify the exponent. The exponent can range from -130 to 125.
• f or F indicates that the number is a 32-bit binary floating point number of type BINARY_FLOAT.
• d or D indicates that the number is a 64-bit binary floating point number of type BINARY_DOUBLE.

Examples of some valid NUMBER literals:

• 100
• +15.26
• 0.25
• 25e-04
• -125

Examples of some valid floating-point number literals:

• 35f
• +5.26F
• 0.2d
• -1D

You can also use the following supplied floating-point literals in situations where a value cannot be expressed as a numeric literal:

Sample table: employees

binary_float_nan:

A value of type BINARY_FLOAT for which the condition IS NAN is true

Example:

SQL> SELECT COUNT(*) FROM employees 
WHERE TO_BINARY_FLOAT(commission_pct) != BINARY_FLOAT_NAN;

Sample Output:

   COUNT(*)
 ----------
 35

binary_float_infinity:

Variable-length Unicode character string having maximum length size characters.

Example:

SQL> SELECT COUNT(*) FROM employees 
WHERE salary < BINARY_FLOAT_INFINITY;

Sample Output:

   COUNT(*)
 ----------
 107

binary_double_nan:

A value of type BINARY_DOUBLE for which the condition IS NAN is true

Example:

SQL> SELECT COUNT(*) FROM employees 
WHERE TO_BINARY_FLOAT(commission_pct) != BIN
ARY_DOUBLE_NAN;

Sample Output:

   COUNT(*)
 ----------
 35

binary_double_infinity:

Double-precision positive infinity.

Example:

SQL> SELECT COUNT(*)
2    FROM employees
  
3    WHERE salary < BINARY_DOUBLE_INFINITY;

Sample Output:

   COUNT(*)
 ----------
 107

Datetime Literals

Oracle Database supports following datetime data types:

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

DATE Literals:

You can specify a DATE value as a string literal, or you can convert a character or numeric value to a date value with the TO_DATE function.

To specify a DATE value as a literal, you must use the Gregorian calendar. You can specify an ANSI literal in the following way :

DATE '2015-01-02'

There is no time portion in ANSI data literal and must be specified in the format 'YYYY-MM-DD'. Alternatively, you can specify an Oracle date value in the following way :

TO_DATE('15-JAN-02 17:30','YY-MON-DD HH24:MI')			

The default date format for an Oracle DATE value is specified by the initialization parameter NLS_DATE_FORMAT. The above example date format includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation. Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions.

If a date value is specified without a time component, then the default time will be midnight (00:00:00 or 12:00:00 for 24-hour and 12-hour clock time, respectively).

If a date value is specified without a date, then the default date is the first day of the current month.

Oracle DATE columns always contain both the date and time fields. Therefore, if you query a DATE column, then you must either specify the time field in your query or ensure that the time fields in the DATE column are set to midnight. Otherwise, Oracle may not return the query results you expect. You can use the TRUNC date function to set the time field to midnight, or you can include a greater-than or less-than condition in the query instead of an equality or inequality condition.

Here are some examples that assume a table test with a number column row_num and a DATE column datecol:

SQL> INSERT INTO test VALUES (SYSDATE);
1 row created.
SQL> SELECT * FROM TEST;

Sample Output:

DATE_COL
---------
02-JAN-15

SQL> SELECT *
FROM TEST
WHERE DATE_COL > TO_DATE('02-JAN-15','DD-MON-YY');

Sample Output:

DATE_COL
---------
02-JAN-15

If you know that the time fields of your DATE column are set to midnight, then you can query your DATE column as shown in the immediately preceding example, or by using the DATE literal:

SQL> SELECT *
2  FROM TEST
3  WHERE DATE_COL = DATE '2015-01-02';

Sample Output:

DATE_COL
---------
02-JAN-15

However, if the DATE column contains values other than midnight, then you must filter out the time fields in the query to get the correct result. For example:

SQL> SELECT *
2    FROM TEST
3    WHERE TRUNC(date_col) = DATE '2015-01-02';

Sample Output:

DATE_COL
---------
02-JAN-15
02-JAN-15

Oracle applies the TRUNC function to each row in the query, so performance is better if you ensure the midnight value of the time fields in your data. To ensure that the time fields are set to midnight, use one of the following methods during inserts and updates:

• Use the TO_DATE function to mask out the time fields:

  INSERT INTO TEST
  2  VALUES (3, TO_DATE('02-01-2014' 'DD-MON-YYYY'));
  

• Use the DATE literal:

  INSERT INTO TEST
    VALUES (4, '02-JAN-15');
  

• Use the TRUNC function:

  INSERT INTO TEST
    VALUES (5, TRUNC(SYSDATE));
  

The date function SYSDATE returns the current system date and time. The function CURRENT_DATE returns the current session date. For information on SYSDATE, the TO_* datetime functions, and the default date format, see "Datetime Functions".

TIMESTAMP Literals:

The TIMESTAMP data type stores following values:

• year
• month
• day
• hour
• minute
• second
• fractional second

When you specify TIMESTAMP as a literal, the fractional_seconds_precision value can be any number of digits up to 9, as follows:

TIMESTAMP '1997-01-31 09:26:50.124'

TIMESTAMP WITH TIME ZONE Literals :

The TIMESTAMP WITH TIME ZONE data type is a variant of TIMESTAMP that includes a time zone region name or time zone offset. When you specify TIMESTAMP WITH TIME ZONE as a literal, the fractional_seconds_precision value can be any number of digits up to 9. For example:

TIMESTAMP '1997-01-31 09:26:56.66 +02:00'

Two TIMESTAMP WITH TIME ZONE values is considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the data. For example,

TIMESTAMP '1999-04-15 8:00:00 -8:00'

is the same as

TIMESTAMP '1999-04-15 11:00:00 -5:00'

8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern Standard Time.

You can replace the UTC offset with the TZR (time zone region name) format element. For example, the following example has the same value as the preceding example:

TIMESTAMP '1999-04-15 8:00:00 US/Pacific'

To eliminate the ambiguity of boundary cases when the daylight saving time switches, use both the TZR and a corresponding TZD format element. The following example ensures that the preceding example will return a daylight saving time value:

TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'

You can also express the time zone offset using a datetime expression:

SELECT TIMESTAMP '2009-10-29 01:30:00' AT TIME ZONE 'US/Pacific'
  FROM DUAL;

If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If that parameter is set to FALSE, then Oracle interprets the ambiguous datetime as standard time in the specified region.

TIMESTAMP WITH LOCAL TIME ZONE Literals

The TIMESTAMP WITH LOCAL TIME ZONE data type differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone. The time zone offset is not stored as part of the column data. There is no literal for TIMESTAMP WITH LOCAL TIME ZONE. Rather, you represent values of this data type using any of the other valid datetime literals. The table that follows shows some of the formats you can use to insert a value into a TIMESTAMP WITH LOCAL TIME ZONE column, along with the corresponding value returned by a query.

Notice that if the value specified does not include a time component (either explicitly or implicitly), then the value returned defaults to midnight.

Interval Literals

An interval literal specifies a period of time. You can specify these differences in terms of years and months, or in terms of days, hours, minutes, and seconds. Oracle Database supports two types of interval literals, YEAR TO MONTH and DAY TO SECOND. Each type contains a leading field and may contain a trailing field. The leading field defines the basic unit of date or time being measured. The trailing field defines the smallest increment of the basic unit being considered. For example, a YEAR TO MONTH interval considers an interval of years to the nearest month. A DAY TO MINUTE interval considers an interval of days to the nearest minute.

If you have date data in numeric form, then you can use the NUMTOYMINTERVAL or NUMTODSINTERVAL conversion function to convert the numeric data into interval values.

Interval literals are used primarily with analytic functions.

INTERVAL YEAR TO MONTH:

Specify YEAR TO MONTH interval literals using the following syntax:

INTERVAL 'integer [- integer ]' { YEAR | MONTH } [ (precision) ] [ TO { YEAR | MONTH } ]

where

• 'integer [-integer]' specifies integer values for the leading and optional trailing field of the literal. If the leading field is YEAR and the trailing field is MONTH, then the range of integer values for the month field is 0 to 11.
• precision is the maximum number of digits in the leading field. The valid range of the leading field precision is 0 to 9 and its default value is 2.

Restriction on the Leading Field

If you specify a trailing field, then it must be less significant than the leading field. For example, INTERVAL '0-1' MONTH TO YEAR is not valid.

The following INTERVAL YEAR TO MONTH literal indicates an interval of 123 years, 2 months:

INTERVAL '123-2' YEAR(3) TO MONTH

Examples of the other forms of the literal follow, including some abbreviated versions:

You can add or subtract one INTERVAL YEAR TO MONTHliteral to or from another to yield another INTERVAL YEAR TOMONTH literal. For example:

INTERVAL '5-3' YEAR TO MONTH + INTERVAL'20' MONTH = 
INTERVAL '6-11' YEAR TO MONTH

INTERVAL DAY TO SECOND

Specify DAY TO SECOND interval literals using the following syntax:

INTERVAL '{ integer | integer time_expr | time_expr }' { { DAY | HOUR | MINUTE } [ (leading_precision) ] | SECOND [ (leading_precision [, fractional_seconds_precision ]) ] } [ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]

where

• integer specifies the number of days. If this value contains more digits than the number specified by the leading precision, then Oracle returns an error.
• time_expr specifies a time in the format HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], where n specifies the fractional part of a second. If n contains more digits than the number specified by fractional_seconds_precision, then n is rounded to the number of digits specified by the fractional_seconds_precision value. You can specify time_expr following an integer and a space only if the leading field is DAY.
• leading_precision is the number of digits in the leading field. Accepted values are 0 to 9. The default is 2.
• fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 1 to 9. The default is 6.

Restriction on the Leading Field:

If you specify a trailing field, then it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid. As a result of this restriction, if SECOND is the leading field, the interval literal cannot have any trailing field.

The valid range of values for the trailing field are as follows:

• HOUR: 0 to 23
• MINUTE: 0 to 59
• SECOND: 0 to 59.999999999

Examples of the various forms of INTERVAL DAY TO SECOND literals follow, including some abbreviated versions:

You can add or subtract one DAY TO SECOND interval literal from another DAY TO SECOND literal. For example.

INTERVAL'20' DAY - INTERVAL'240' HOUR = INTERVAL'10-0' DAY TO SECOND

Previous: Data Types
Next: Unary and Binary Operators