Precision of Interval for PL/SQL Function value
- by Gary
Generally, when you specify a function the scale/precision/size of the return datatype 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 function return value is unrestricted. This is documented functionality.
Now, I get an precision error (ORA-01873) if I push 9999 hours (about 400 days) into the following. The limit is because the default days precision 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 it won't allow the precision to be specified 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;
/
I can use a 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 drawbacks to the subtype approach ?
Any alternatives (eg some place to change a default precision) ?
Working with 10gR2.
