create or replace function round_interval (p_interval dsinterval_unconstrained,
p_unit dsinterval_unconstrained)
return dsinterval_unconstrained
is
l_result dsinterval_unconstrained ;
begin
select case
when p_interval < p_unit / 2 then numtodsinterval(0,'second')
when p_interval < p_unit then p_unit
when p_interval - n.k * p_unit < p_unit / 2 then n.k * p_unit
else (n.k + 1) * p_unit
end
into l_result
from
(select max(level) k from dual
connect by level * p_unit <= p_interval) n
;
return l_result;
end; Function created.
select round_interval( interval '10' minute, interval '5' minute ) rnd
from dual | RND | +000000000 00:10:00.000000000 |
|---|
select localtimestamp - trunc(localtimestamp) int,
round_interval( localtimestamp - trunc(localtimestamp), interval '5' minute ) rnd
from dual | INT | RND | +000000000 14:14:11.671948 | +000000000 14:15:00.000000000 |
|---|
create or replace function round_interval_macro (p_interval interval day to second, -- dsinterval_unconstrained,
p_unit interval day to second) -- dsinterval_unconstrained)
return varchar2 sql_macro -- (table)
is
begin
return q'{select case
when p_interval < p_unit / 2 then numtodsinterval(0,'second')
when p_interval < p_unit then p_unit
when p_interval - n.k * p_unit < p_unit / 2 then n.k * p_unit
else (n.k + 1) * p_unit
end
from
(select max(level) k from dual
connect by level * p_unit <= p_interval) n
}' ;
end; Function created.
select *
from round_interval_macro( interval '10' minute, interval '5' minute ) ORA-62565: The SQL Macro method failed with error(s).
ORA-06550: line 5, column 65:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or default
like like2 like4 likec as between from to using || multiset
member submultisetMore Details: https://docs.oracle.com/error-help/db/ora-62565
create or replace function round_interval_scalar (p_interval interval day to second, -- dsinterval_unconstrained,
p_unit interval day to second) -- dsinterval_unconstrained)
return varchar2 sql_macro (scalar)
is
begin
return q'{ (select case
when p_interval < p_unit / 2 then numtodsinterval(0,'second')
when p_interval < p_unit then p_unit
when p_interval - n.k * p_unit < p_unit / 2 then n.k * p_unit
else (n.k + 1) * p_unit
end
from
(select max(level) k from dual
connect by level * p_unit <= p_interval) n
) }' ;
end; Errors: FUNCTION ROUND_INTERVAL_SCALAR
Line/Col: 3/45 PLS-00103: Encountered the symbol "(" when expecting one of the following:
; is default authid as cluster order using external
deterministic parallel_enable pipelined aggregate
result_cache accessible rewrite
More Details: https://docs.oracle.com/error-help/db/ora-24344
select round_interval_scalar ( interval '10' minute, interval '5' minute )
from dual ORA-06575: Package or function ROUND_INTERVAL_SCALAR is in an invalid stateMore Details: https://docs.oracle.com/error-help/db/ora-06575