We’ve been wrestling with this problem for a while now, and I’ve finally figured out the cause of the error message, and work around of what seems to be a bug or inherent restriction of Oracle functions.
The calling code is:
:new.duration := interval_to_seconds(:new.sample_timestamp - v_state_r.record_timestamp);
The original error was:
ORA-01873: the leading precision of the interval is too small
which traced back to a line which subtracted to timestamps and passed the result to a function who’s signature is:
FUNCTION interval_to_seconds(x INTERVAL DAY TO SECOND ) RETURN NUMBER IS
This worked _most_ of the time. But it was consistent – which is a mile better than not working consistently.
I finally got around to tracking down the two problematic timestamps (which was a lot of work, it’s buried in a trigger, inside a transaction).
That didn’t really help by itself so I studied up on the Interval data type.
Hunting down information about Oracle data types, I finally found this documentation. The bit that sparked me off was this section:
You use the datatype INTERVAL DAY TO SECOND to store and manipulate intervals of days, hours, minutes, and seconds. The syntax is:
INTERVAL DAY[(leading_precision)] TO
SECOND[(fractional_seconds_precision)]where leading_precision and fractional_seconds_precision specify the number of digits in the days field and seconds field, respectively. In both cases, you cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The defaults are 2 and 6, respectively.
Notably, this quote: The defaults are 2 and 6, respectively..
Turns out that the result of the subtraction was *sometimes* quite large. The threshold was of course, two digits in the day field. I.e. 99 days vs 100 days.
Two days seems like a stupid default. So of course, let’s explicitly specify the _leading precision_ then:
FUNCTION interval_to_seconds(x INTERVAL DAY<strong>(9)</strong> TO SECOND<strong>(6)</strong> ) RETURN NUMBER IS
And of course, knowing my luck, that doesn’t compile:
PLS-00103: Encountered the symbol "(" when expecting one of the following: to
Scratched my head over that for too long, and thought of a work around – pass the two dates into the function, and create the interval as a local variable, where we can specify the precision.
FUNCTION timestamp_diff_in_seconds(timeOne timestamp, timeTwo timestamp) RETURN NUMBER IS
x INTERVAL DAY(9) TO SECOND(6);
...
x := timeTwo - timeOne;
This corrects the problem. If anyone knows why I can’t specify the precision of my interval in the function signature, please post!The other really bizzare thing, is that calling :new.duration := interval_to_seconds('+000003499 23:59:44.600000'); directly from sqlplus or sqlDeveloper does not through an error! It returns 302399985, which is a worry because the milliseconds have gone!
Btw – for the record:
Oracle version is:
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 31 08:20:08 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production
Database product name : Oracle
Database product version : Oracle Database 10g Release 10.2.0.3.0 - Production
JDBC driver name : Oracle JDBC driver
JDBC driver version : 10.2.0.3.0
DataStoreHelper name is: com.ibm.websphere.rsadapter.Oracle10gDataStoreHelper@1de81de8.
JDBC driver type :
[edited] corrected original function signature
Stubbisms - Tony's Weblog 