Interval Accuracy for PL / SQL Function Value

Question

Usually, when you specify a function, the scale / precision / size of the returned data type 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 return value of the function is not limited. This is documented functionality.

Now I get a precision error (ORA-01873) if I press 9999 hours (about 400 days) in the following. The limit is that the default day limit is 2

 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; / 

and this will not allow accurate precision to be indicated as part of the data type 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; / 

I can use SUBTYPE

 DECLARE subtype t_int is INTERVAL DAY (4) TO SECOND(0); v_int INTERVAL DAY (4) TO SECOND(0); FUNCTION hhmm_to_interval return t_int 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; / 

Any flaws in the subtype approach?

Any alternatives (e.g. some place to change the default accuracy)?

Work with 10gR2.

Answer 1

No real flaws that I can think of. I think it would be a little more clear if the working variables were declared as instances of the subtype, for example:

 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=' || v_int); end; 

Share and enjoy.

Answer 2

Oracle provides some built-in subtypes for this purpose; see Avoiding Truncation Issues Using Date and Time Subtypes

It:

 TIMESTAMP_UNCONSTRAINED TIMESTAMP_TZ_UNCONSTRAINED TIMESTAMP_LTZ_UNCONSTRAINED YMINTERVAL_UNCONSTRAINED DSINTERVAL_UNCONSTRAINED