Hi,
I am debugging someone else's PL/SQL code and ran into an issue I do not (fully) understand.
There is a function inside a package that is called very often. The function executes a select on a table and returns a value. For performance reasons the last known result of the function is cached in a package variable (along with a timestamp). If a new call is made to the function and the cached value is less than 5 seconds old, the function should return the cached value. If the cached value is more than 5 seconds old, the function should query the table again and cache the value.
This is the essence of the logic
create or replace package ...
v_value number; -- package variable contains the cached value
v_timestamp timestamp := null; -- timestamp when v_value was last read
v_timeout number := 5000; -- cache timeout, assuming the original developer meant milliseconds
function get_value
return number
is
begin
if v_timestamp is null or systimestamp - v_timestamp > v_timeout then
select value into v_value from table;
v_timestamp := systimestamp;
end if;
return v_value;
end;
end;
My question is about the second part of line 10:
systimestamp - v_timestamp > v_timeout
It compiles, it does not generate an runtime exception but it does not work. Only the first time, when v_timestamp is null, the function will query the table. All other times it will return the cached value. The value is never refreshed.
And I understand that it does not work: systimestamp - v_timestamp is an interval. v_timeout is a number. They are two different data types.
There must be some kind of implicit conversion going on, but I can not figure out what it is.
I tried to cast the interval to number, but that raises an exception. I tried to cast the number to interval, but that also does not work.
How does Oracle evaluate this expression?
Of course it can be easily fixed by using the correct datatypes. I am just confused as to how this bug could have been in the code for almost 5 years and nobody has noticed it. Also I want to know more about the way Oracle evaluates expressions with different datatypes. If there is implicit conversion going on, is there a way to make it explicit so we can know for sure what is being compared?
I'm on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
Many thanks!
Rop