Unconstrained INTERVAL types

Intervals can be declared with varying levels of precision, and values of different precisions are not entirely compatible with each other. Look at the following example, which shows how fractional-seconds digits can be lost as a result of careless assignment between two variables declared slightly differently:

DECLARE A INTERVAL DAY TO SECOND; C INTERVAL DAY(9) TO SECOND(2);BEGIN A := '10 0:0:0.123456'; C := A; DBMS_OUTPUT.PUT_LINE (A); DBMS_OUTPUT.PUT_LINE (C);END;

The output is:

+10 00:00:00.123456+000000010 00:00:00.12

The value assigned to A specifies 0.123456 seconds, which works because the default precision for INTERVAL DAY TO SECOND variables is six digits past the decimal point. When the value of A is assigned to C, however, four of those digits were lost because of the way C was declared.

At first, this issue seems to be just a matter of keeping your datatypes straight, but it becomes a problem when writing procedures and functions that accept INTERVAL values as parameters. Notice the loss of precision when the value of B is doubled via a call to the function double_my_interval:

DECLARE B INTERVAL DAY(9) TO SECOND(9); FUNCTION double_my_interval ( A IN INTERVAL DAY TO SECOND) RETURN INTERVAL DAY TO SECOND IS BEGIN RETURN A * 2; END;BEGIN B := '1 0:0:0.123456789'; DBMS_OUTPUT.PUT_LINE(B); DBMS_OUTPUT.PUT_LINE(double_my_interval(B));END;

The output is:

+000000001 00:00:00.123456789+02 00:00:00.246914

Not only have we lost digits in our fractional seconds, but we've also lost digits where the number of days is concerned. Had B been assigned a value of 100 days or more, the call to double_my_interval would have failed with an ORA-01873: the leading precision of the interval is too small error.

The issue here is that the default precision for INTERVAL types is not the same as the maximum precision. This problem is compounded by the fact that PL/SQL doesn't allow us to specify precision when declaring a function's or procedure's formal parameters. We can't, for example, do the following:

FUNCTION double_my_interval ( A IN INTERVAL DAY(9) TO SECOND(9)) RETURN INTERVAL DAY TO SECOND IS

Fortunately, the good people who design PL/SQL had the foresight to provide us with two unconstrained datatypes just to solve this particular problem. These datatypes are:

YMINTERVAL_UNCONSTRAINED

Accepts any INTERVAL YEAR TO MONTH value with no loss of precision

DSINTERVAL_UNCONSTRAINED

Accepts any INTERVAL DAY TO SECOND value with no loss of precision

Using the DSINTERVAL_UNCONSTRAINED type, we can recode our earlier example as follows:

DECLARE B INTERVAL DAY(9) TO SECOND(9); FUNCTION double_my_interval ( A IN DSINTERVAL_UNCONSTRAINED) RETURN DSINTERVAL_UNCONSTRAINED IS BEGIN RETURN A * 2; END;BEGIN B := '100 0:0:0.123456789'; DBMS_OUTPUT.PUT_LINE(B); DBMS_OUTPUT.PUT_LINE(double_my_interval(B));END;

The output is:

+000000100 00:00:00.123456789+000000200 00:00:00.246913578

Notice that we used DSINTERVAL_UNCONSTRAINED twice: once to specify the type of the formal parameter to double_my_interval, and once to specify the function's return type. As a result, we can now invoke the function on any INTERVAL DAY TO SECOND value with no loss of precision.

10.4 Date/Time Functions

Date arithmetic, described in the previous section, does not provide all the functionality you need when working with datetime values, so PL/SQL also implements a number of helpful, built-in datetime functions. You've already read about built-in conversion functions such as TO_DATE and NUMTOYMINTERVAL in previous sections. Table 10-5 briefly describes the other date-related functions at your disposal.

Avoid using Oracle's traditional DATE functions with the new timestamp types. Instead, use the new interval functionality whenever possible. Use DATE functions only with DATE values.

Many of the functions in the table accept DATE values as inputs. ADD_MONTHS is an example of one such function. You must be careful when you consider using such functions to operate on any of the new TIMESTAMP datatypes. While you can pass a TIMESTAMP type to one of these functions, Oracle implicitly converts that type to a DATE. Only then does the function perform its operation. As a result of this implicit conversion, fractional seconds and any time zone information will be lost. The result type from such functions will be of type DATE. For example, if you pass a TIMESTAMP WITH TIME ZONE to ADD_MONTHS, you will get back a DATE without any time zone information and without any fractional seconds. Be careful!