-- OTRCFUNC.SQL
--
-- Defines the elapsed function for calculating elapsed time for Trace duration
-- events.  Returns elapsed time in units of seconds.  It needs the collection 
-- name to get the number of units in the nano fields.
--
-- NOTE however that as of 8.1 the Trace formatter will create and fill
--      an ELAPSED_TIME column for each duration event, so there should
--      be no need to calculate elapsed time via this function.
--
-- Example:  select avg(elapsed('oracle7', 
--                          x.timestamp_start, x.timestamp_nano_start,
--                          x.timestamp_end, x.timestamp_nano_end))
--           from v_192216243_f_5_e_9_8_1 x, epc_collection c
--           where c.collection_name = 'oracle7' and
--                 c.collection_id = x.collection_number;

CREATE OR REPLACE FUNCTION elapsed 
                           (coll_name VARCHAR2, 
                            start_time DATE,
                            start_nanos NUMBER,
                            end_time DATE,
                            end_nanos NUMBER)
   RETURN NUMBER 
AS
    ms_units NUMBER;                                -- # nanos per second
    nanos NUMBER;
    new_end_time DATE;
    time NUMBER;
    seconds NUMBER;
BEGIN
    SELECT ms_granularity INTO ms_units FROM epc_collection
        WHERE collection_name = coll_name;
    new_end_time := end_time;
    nanos := end_nanos - start_nanos;
    IF nanos < 0 THEN
        new_end_time := end_time - (1/(60*60*24));  -- subtract 1 second
        nanos := (ms_units + end_nanos) - start_nanos;
    END IF;
    time := new_end_time - start_time;              -- in units of days
    seconds := time * (60*60*24);                   -- days * seconds/day
    seconds := seconds + (nanos/ms_units);
    RETURN(seconds);
END;
/
