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

About these ads

8 Comments »

  1. **shudder**
    and i thought my stupid OpenGL context problems were bad…

    Comment by Nathan — November 15, 2007 @ 10:56 am

  2. Oh there’s worse… trust me… I’m just getting started…

    Comment by Antony Stubbs — November 21, 2007 @ 11:02 am

  3. Default for Pl/Sql types does not have anything to do with function parameters. You don’t need to specify precision for function parameters because functions accept any precision. The same thing goes for numbers and varchar, you will never define function as some_function(X NUMBER(10)) IS …, you just use NUMBER. But in SQL and PL/SQL you have some default values. For example you can define column as VARCHAR2(4000) since 4000 is max size, or in PL/SQL you can use VARCHAR2(32767) since it is max for pl/sql but as a function parameter you use just VARCHAR.

    Now, this is the function that you need:

    create or replace
    FUNCTION fn_interval_to_seconds (p_interval INTERVAL DAY TO SECOND) RETURN NUMBER IS
    BEGIN
    RETURN EXTRACT(DAY FROM p_interval)*86400 + EXTRACT(HOUR FROM p_interval)*3600 + EXTRACT(MINUTE FROM p_interval)*60 + EXTRACT(SECOND FROM p_interval);
    END fn_interval_to_seconds;

    and if you run this:

    select fn_interval_to_seconds('+000003499 23:59:44.60000')
    from dual;

    you get: 302399984.6

    Also if you run this you will get the same result:

    DECLARE
    checkin TIMESTAMP(3);
    checkout TIMESTAMP(3);
    a NUMBER(38,3);
    BEGIN
    checkin := systimestamp;
    checkout := checkin + INTERVAL '+000003499 23:59:44.60000' DAY TO SECOND;
    select fn_interval_to_seconds(checkout-checkin) into a
    from dual;
    dbms_output.enable;
    dbms_output.put_line(a);
    end;

    Comment by Srdjan — December 27, 2007 @ 1:13 am

  4. Do you know which tags can I use in comments here?

    Comment by Srdjan — December 27, 2007 @ 1:15 am

  5. @Srdjan
    Well that’s what I thought :)
    Just a correction, the original function signature didn’t actually define any precision values – my mistake.
    I will definitely try out what you’re suggesting here sometime in the new year, but from what I can see i would expect it to cause the same problems I was originally having. Interesting to see it seems to work for you though…

    Your algorithm also looks much better than what we’ve been using:

    CREATE OR REPLACE FUNCTION interval_to_seconds(x INTERVAL DAY TO SECOND ) RETURN NUMBER IS
    s VARCHAR2(26);
    days_s VARCHAR2(26);
    time_s VARCHAR2(26);
    N NUMBER;
    BEGIN
    s := TO_CHAR(x);
    days_s := SUBSTR(s,2,INSTR(s,' ')-2);
    time_s := SUBSTR(s,2+LENGTH(days_s)+1);
    N := 86400*TO_NUMBER(days_s) + 3600*TO_NUMBER(SUBSTR(time_s,1,2)) + 60*TO_NUMBER(SUBSTR(time_s,4,2)) + TO_NUMBER(SUBSTR(time_s,7));
    IF SUBSTR(s,1,1) = '-' THEN
    N := - N;
    END IF;
    RETURN N;
    END;

    This is also seems to be the more common one on web sites, so it’s good to see a much more readable version! I’m not sure where our DBA got our one from, but I’ll be sure to show him your suggestion!

    Note the above didn’t work for interval’s larger than 99 days, which was replaced by the below:
    create or replace

    FUNCTION timestamp_diff_in_seconds(timeOne timestamp, timeTwo timestamp) RETURN NUMBER IS
    x INTERVAL DAY(9) TO SECOND(6);
    s VARCHAR2(26);
    days_s VARCHAR2(26);
    time_s VARCHAR2(26);
    N NUMBER;
    BEGIN
    x := timeTwo - timeOne;
    s := TO_CHAR(x);
    days_s := SUBSTR(s,2,INSTR(s,' ')-2);
    time_s := SUBSTR(s,2+LENGTH(days_s)+1);
    N := 86400*TO_NUMBER(days_s) + 3600*TO_NUMBER(SUBSTR(time_s,1,2)) + 60*TO_NUMBER(SUBSTR(time_s,4,2)) + TO_NUMBER(SUBSTR(time_s,7));
    IF SUBSTR(s,1,1) = '-' THEN
    N := - N;
    END IF;
    RETURN N;
    END;

    Comment by Antony Stubbs — December 27, 2007 @ 1:53 am

  6. Oh and the -code- tags seem to work fine.

    Comment by Antony Stubbs — December 27, 2007 @ 1:54 am

  7. It’s funny, your function works here :-)
    It seems that problem is not in the function itself but in the way Oracle deals with timestamp subtraction. It uses default interval type and that happens before it calls that function so maybe you can subtract timestamps before you call interval_to_seconds. Just declare x in the same way you declared x in the second function and then subtract timestamps into x before you call interval_to_seconds(x).

    Have fun

    Comment by Srdjan Mitrovic — December 27, 2007 @ 11:17 am

  8. In PL/SQL there is the predefined subtype DSINTERVAL_UNCONSTRAINED. This can be used in paramter definitions.

    CREATE FUNCTION intervalTest (lag_time DSINTERVAL_UNCONSTRAINED) RETURN NUMBER IS
    BEGIN
    return extract (DAY FROM lag_time);
    end;

    Comment by stezgr — January 7, 2009 @ 1:12 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 686 other followers

%d bloggers like this: