Stubbisms – Tony’s Weblog

November 7, 2007

Oracle Intervals and Function signatures

Filed under: Database, Java, Oracle — Tags: , , , , , , — Antony Stubbs @ 4:54 pm

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

The Shocking Blue Green Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 686 other followers