Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 1 Oracle SQL Basics
  5. 1.3.4 Literals

1.3.4 Literals

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 LiteralValid / 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 LiteralValid / Invalid
Numeric LiteralValid / Invalid
2345Valid
2345.987Valid
+231.678Valid
+0.231Valid
+0.23100000Valid
-0.231Valid
23 45.987Invalid – Space not allowed
23-A-1Invalid – 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 LiteralsValid/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 LiteralsValid/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 LiteralsValid/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 LiteralsMeaning
INTERVAL ’99’ YEARAn interval of 99 years 0 months
INTERVAL ‘100’ YEARInvalid 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 MONTHAn 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’ HOURAn interval of 10 hours
INTERVAL ’35’ MINUTEAn interval of 35 minutes
INTERVAL ’10:35′ MINUTE TO SECONDAn 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
Was this article helpful to you? Yes No

How can we help?