Interval literals are similar to timestamp literals and are useful when you want to embed interval values as constants within your code. Interval literals take the following form:
Is the character string representation of the interval. See Interval Conversions for a description of how the two interval datatypes are represented in character form.
Specifies the leading element in the interval.
Specifies the trailing element in the interval.
Unlike the TO_YMINTERVAL and TO_DSINTERVAL functions, interval literals allow you to specify an interval using any sequence of datetime elements from Table 10-2. There are only two restrictions:
You must use a consecutive sequence of elements.
You cannot transition from a month to a day within the same interval.
Following are several valid examples:
DECLARE y2ma INTERVAL YEAR TO MONTH; y2mb INTERVAL YEAR TO MONTH; d2sa INTERVAL DAY TO SECOND; d2sb INTERVAL DAY TO SECOND; BEGIN /* Some YEAR TO MONTH examples */ y2ma := INTERVAL '40-3' YEAR TO MONTH; y2mb := INTERVAL '40' YEAR; /* Some DAY TO SECOND examples */ d2sa := INTERVAL '10 1:02:10.123' DAY TO SECOND; /* Fails in Oracle9i through 11gR2 because of a bug */ --d2sb := INTERVAL '1:02' HOUR TO MINUTE; /* Following are two workarounds for defining intervals, such as HOUR TO MINUTE, that represent only a portion of the DAY TO SECOND range. */ SELECT INTERVAL '1:02' HOUR ...