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.
The %TYPE attribute lets use the datatype of a field, record, nested table, database column, or variable in your own declarations, rather than hardcoding the type names. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters.
Introduction to Oracle data type There are two types of INTERVAL : INTERVAL YEAR TO MONTH – stores intervals using of year and month. INTERVAL DAY TO SECOND – stores intervals using days, hours, minutes, and seconds including fractional seconds.
The %TYPE attribute, used in PL/SQL variable and parameter declarations, is supported by the data server. Use of this attribute ensures that type compatibility between table columns and PL/SQL variables is maintained.
For numeric types, the default is 0 , with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence.
No real drawbacks that I can think of. I think it would be a bit more clear if the working variables were declarred as instances of the subtype, e.g.:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With