Precision of Interval for PL/SQL Function value

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.

---

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, eg:

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.

---

Oracle provides some build-in subtypes for that purpose, see Avoiding Truncation Problems Using Date and Time Subtypes

They are:

TIMESTAMP_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
YMINTERVAL_UNCONSTRAINED
DSINTERVAL_UNCONSTRAINED

链接地址: http://www.djcxy.com/p/46122.html

上一篇: 如何使用Google appengine项目在Eclipse中引用另一个项目？

下一篇: PL / SQL函数值的间隔精度