Long intervals, PL/SQL Functions and subtypes.

I was doing some work with the interval day to second datatype recently. I hit a problem and thought it worth documenting my solution.

Generally, when you create a PL/SQL function or parameter, the scale/precision/size of the parameters (including the return value of a function) is undefined.

For example, you say FUNCTION show_price RETURN NUMBER or FUNCTION show_name RETURN VARCHAR2. You are not allowed to have FUNCTION show_price RETURN NUMBER(10,2) or FUNCTION show_name RETURN VARCHAR2(20), and the length/precision/scale is supposedly derived from the calling environment.

This new fangled INTERVAL DAY TO SECOND datatype is a bit odd, in that it has a default precision of 2 for the days.  That is, it can only store a number of days up to two digits (99 days). So if you have a function with a INTERVAL DAY TO SECOND return type, it will balk at a value of 9999 hours (over 400 days), and throw a ORA-01873 precision error, even if the precision of the variable in the calling environment is sufficient.

DECLARE
  v_int INTERVAL DAY (4) TO SECOND(0);
  FUNCTION hhmm_to_interval return INTERVAL DAY TO SECOND IS
    v_hhmm INTERVAL DAY (4) TO SECOND(0);
  BEGIN
    v_hhmm := to_dsinterval('PT9999H');
    RETURN v_hhmm;
    --
  END hhmm_to_interval;
BEGIN
  v_int := hhmm_to_interval;
end;
/

ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at line 7
ORA-06512: at line 11

You are not allowed to specify the precision directly as part of the datatype returned by the function.

DECLARE
  v_int INTERVAL DAY (4) TO SECOND(0);
  FUNCTION hhmm_to_interval return INTERVAL DAY (4) TO SECOND IS
    v_hhmm INTERVAL DAY (4) TO SECOND(0);
  BEGIN
    v_hhmm := to_dsinterval('PT9999H');
    RETURN v_hhmm;
    --
  END hhmm_to_interval;
BEGIN
  v_int := hhmm_to_interval;
end;
/
ORA-06550: line 3, column 49:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
to

You can use a PL/SQL SUBTYPE however.

DECLARE
  subtype t_int is INTERVAL DAY (4) TO SECOND(0);
  v_int t_int;
  FUNCTION hhmm_to_interval return t_int IS
    v_hhmm t_int;
  BEGIN
    v_hhmm := to_dsinterval('PT9999H');
    RETURN v_hhmm;
    --
  END hhmm_to_interval;
BEGIN
  v_int := hhmm_to_interval;

  dbms_output.put_line(v_int);
end;
/
+0416 15:00:00

PL/SQL procedure successfully completed.

The documentation is a bit skimpy here though. It states that where a parameter is based on a subtype, a NOT NULL constraint on the subtype is applied to the parameter. It also states that for numbers and VARCHAR2 it does NOT apply the size/scale/precision constraints (though it will apply a range constraint for a numeric type).

I added a comment for the documentation with the hope that the INTERVAL datatypes get a specific mention.

A comment by stezgr on the same issue raised on the stubbisms blog pointed out a built-in DSINTERVAL_UNCONSTRAINED subtype for this. When searching on that, Google brings up Steven Feuerstein's Oracle PL/SQL Programming book which describes using that built-in as the solution.