Integer Literals

You must use the integer notation to specify an integer whenever integer appears in expressions, conditions, SQL functions, and SQL statements described in other parts of this reference.

The syntax of integer is as follows:

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:

7
+255

NUMBER and Floating-Point Literals

You must use the number or floating-point notation to specify values whenever number or n appears in expressions, conditions, SQL functions, and SQL statements in other parts of this reference.

The syntax of number is as follows:

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)

  If you omit f or F and d or D, then the number is of type NUMBER.

  The suffixes f (F) and d (D) are supported only in floating-point number literals, not in character strings that are to be converted to NUMBER. That is, if Oracle is expecting a NUMBER and it encounters the string '9', then it converts the string to the number 9. However, if Oracle encounters the string '9f', then conversion fails and an error is returned.

A number of type NUMBER can store a maximum of 38 digits of precision. If the literal requires more precision than provided by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then Oracle truncates the value. If the range of the literal exceeds the range supported by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then Oracle raises an error.

If you have established a decimal character other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS, then you must specify numeric literals with 'text' notation. In these cases, Oracle automatically converts the text literal to a numeric value.

For example, if the NLS_NUMERIC_CHARACTERS parameter specifies a decimal character of comma, specify the number 5.123 as follows:

'5,123'

Here are some valid NUMBER literals:

25
+6.34
0.5
25e-03
-1

Here are some valid floating-point number literals:

25f
+6.34F
0.5d
-1D

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

SELECT COUNT(*) 
FROM employees 
WHERE TO_BINARY_FLOAT(commission_pct)
IS NOT NAN;

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

SELECT COUNT(*) 
FROM employees 
WHERE TO_BINARY_DOUBLE(commission_pct) IS NOT NAN;

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

INTERVAL YEAR TO MONTH

Specify YEAR TO MONTH interval literals using the following syntax:

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.

If you specify a trailing field, 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 MONTH literal to or from another to yield another INTERVAL YEAR TO MONTH 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:

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.

If you specify a trailing field, 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