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.

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;

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.

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.

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.

## No comments:

Post a Comment