Literals are the values that you use in SQL statements or PL/SQL code. Oracle supports following types of literals.
1.3.4.1 Text Literals
1.3.4.2 Numeric Literals:
1.3.4.3 Datetime Literals
1.3.4.4 Interval Literals
1.3.4.1 Text Literals
Anything in SQL declared within single quotation mark (‘) are Text literals. You can enclose anything, characters, numbers, special characters within single quotes and that becomes text literals.
e.g. ‘Hello World’, ‘This is 2nd article’, ‘Today”s date is 1st January 2019’
NOTE
Text literals doesn’t have valid and invalid values. But with other type of literals you can have invalid values. Numbers like 25, 63.4788, -9.2345 are all valid numeric literals. But ‘A1’ is not a valid numeric literal, it’s a text literal.
| Text Literal | Valid / Invalid |
| ‘ABC’ | Valid |
| ‘the Code Man’ | Valid |
| ‘ ‘ | Valid |
| ‘hero No 1 $’ | Valid |
| ‘1+2=20’ | Valid |
| ‘can I? hooray! :)’ | Valid |
| ’23 45.987′ | Valid |
| ’23-A-1′ | Valid |
1.3.4.2 Numeric Literals:
Numeric literals are used to specify integers and floating point values.
e.g. 25, 63.4788, -9.2345, 0.123, 34.0 are all valid examples of numeric literals.
Text literals doesn’t have valid and invalid values. But with other type of literals you can have invalid values.
| Numeric Literal | Valid / Invalid |
|---|---|
| Numeric Literal | Valid / Invalid |
| 2345 | Valid |
| 2345.987 | Valid |
| +231.678 | Valid |
| +0.231 | Valid |
| +0.23100000 | Valid |
| -0.231 | Valid |
| 23 45.987 | Invalid – Space not allowed |
| 23-A-1 | Invalid – Character not allowed |
1.3.4.3 Datetime Literals
You can specify a DATE value as a string literal in valid datetime formats. The default date format for an Oracle DATE value is ‘DD-MON-YY’. e.g. ’25-JAN-19′ or ’25-JAN-2019′
Date formats In Oracle can be altered by the initialization parameter NLS_DATE_FORMAT.
Oracle Database supports four datetime data types:
- DATE
- TIMESTAMP
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE
Let us understand datetime literals for each data type.
DATE Literals
| Date Literals | Valid/Invalid |
|---|---|
| ’25-JAN-19′ | Valid |
| ’25-JAN-2019′ | Valid |
| ’25-01-19′ | Invalid |
| ’25/JAN/2019′ | Invalid |
| ’25/01/19′ | Invalid |
| ‘2019-JAN-25’ | Invalid |
| ’25-jan-19′ | Valid |
TIMESTAMP Literals
The TIMESTAMP data type stores year, month, day, hour, minute, and second, and fractional second values. Oracle default timestamp format is ‘DD-MON-YY HH:MI:SS.fractional_seconds_precision’
where HH – Hour; MI – Minute; SS – second
When you specify TIMESTAMP as a literal, the fractional_seconds_precision is optional and this value can be any number of digits up to 9.
| Timestamp Literals | Valid/Invalid |
|---|---|
| ’25-JAN-19 06:26:50.125′ | Valid |
| ’25-JAN-2019 06:26:50.125′ | Valid |
| ’25-JAN-2019 18:26:50.125′ | Valid – time in 24 hours format |
| ’25-JAN-2019 65:26:50.125′ | Invalid – Hour can’t exceed 23 |
| ’25-JAN-2019 18:65:50.125′ | Invalid – minute/sec can’t exceed 59 |
| ‘2019-JAN-25 06:26:50.125’ | Invalid – Date format |
| ’25-JAN-2019 06:26:50′ | Valid |
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.
| Timestamp with time zone Literals | Valid/Invalid |
|---|---|
| ’25-JAN-19 06:26:50.125 +02:00′ | Valid |
| ’25-JAN-2019 06:26:50.125 +05:30′ | Valid |
| ’25-JAN-2019 18:26:50.125 -05:30′ | Valid |
| ’25-JAN-2019 18:26:50.125 US/Pacific’ | Valid – Specifying time zone region name is accepted by Oracle |
TIMESTAMP WITH LOCAL TIME ZONE Literals
When data is stored in the database for columns having TIMESTAMP WITH LOCAL TIME ZONE, the time zone offset is not stored as part of the column data.
You can specify any valid date or timestamp literals for TIMESTAMP WITH LOCAL TIME ZONE.
1.3.4.4 Interval Literals
Interval literal specifies a period of time. You can specify INTERVAL literals in terms of years and months, or in terms of days, hours, minutes, and seconds.
Let us understand interval literals through some examples
| Interval Literals | Meaning |
|---|---|
| INTERVAL ’99’ YEAR | An interval of 99 years 0 months |
| INTERVAL ‘100’ YEAR | Invalid interval because ‘100’ has 3 digits which is greater than the default precision (2) |
| INTERVAL ‘100’ YEAR(3) | An interval of 100 years 0 months. YEAR(3) denotes the number of digits in the value. |
| INTERVAL ‘100’ MONTH(3) | An interval of 100 months |
| INTERVAL ‘140-6’ YEAR(3) TO MONTH | An interval of 140 years, 6 months; Must specify the leading field precision YEAR(3) because the value of the leading field is greater than the default precision (2 digits). |
| INTERVAL ‘100’ DAY(3) | An interval of 100 days |
| INTERVAL ’10’ HOUR | An interval of 10 hours |
| INTERVAL ’35’ MINUTE | An interval of 35 minutes |
| INTERVAL ’10:35′ MINUTE TO SECOND | An interval of 10 minutes and 35 seconds |
| INTERVAL ’11 10:35:08.555′ DAY TO SECOND(3) | 11 days, 10 hours, 35 minutes, 08 seconds, and 555 fraction of a second |