If you ever need to display the number of hours, minutes and seconds between two events, this will help.  This little package can be called once to record the start time, then later to display the elapsed time since the initial call.  It has two modes of displaying the time, depending on whether the elapsed time is more or less than 10 seconds.  When it is less than ten seconds, it displays the seconds down to 0.01 second.  This process even corrects for some truncation errors caused by the floating point arithmetic used to extract hours, minutes and seconds.

The output looks like one of these:

      Elapsed time = 9.25 seconds
      Elapsed time (HH:MI:SS) = 00:02:15
Call Elapsed_Time as follows:

      PK2.ELAPSED_TIME(T,'start'); -- to initialize
      PK2.ELAPSED_TIME(T);  -- to retrieve the formatted text.
Here is the package:
 

CREATE OR REPLACE PACKAGE PK2 IS
  PROCEDURE ELAPSED_TIME
    (T IN OUT VARCHAR2,MODE1 IN VARCHAR2 DEFAULT NULL);
END PK2;
.
/
SHOW ERRORS PACKAGE PK2
CREATE OR REPLACE PACKAGE BODY PK2 IS
--------------------------------------
  START_TIME     DATE;
  START_TIMEN    NUMBER;
  --
  PROCEDURE ELAPSED_TIME
  ---------------------------------
    (T IN OUT VARCHAR2,MODE1 IN VARCHAR2 DEFAULT NULL) IS
      ET  NUMBER; -- Elapsed run time
      ETN NUMBER; -- Elapsed time to hundredths of seconds
  BEGIN
    IF MODE1 IS NOT NULL THEN
      START_TIME  := SYSDATE;
      START_TIMEN := DBMS_UTILITY.GET_TIME;
    ELSE
      ET   := SYSDATE - START_TIME + 0.000001;
      ETN  := (DBMS_UTILITY.GET_TIME - START_TIMEN) / 100;
      IF ETN >= 10 THEN
        T:='Elapsed time (HH:MM:SS) = '
              ||Ltrim(To_char(    Trunc(ET*24),        '99900'))||':'
              ||Ltrim(To_char(Mod(Trunc(ET*1440),60),  '00')) ||':'
              ||Ltrim(To_char(Mod(Trunc(ET*86400),60) ,'00'));
      ELSE
        T:='Elapsed time ='
               ||To_char(ETN,'0.00')||' seconds';
      END IF;
    END IF;
  END ELAPSED_TIME;
  --
END PK2; -- End of Package Body --
.
/
SHOW ERRORS PACKAGE BODY PK2

Here is a script to test the process:

Set SERVEROUTPUT ON SIZE 20000
Declare
  T  Varchar2(40);
  X  Date;
Begin
  PK2.ELAPSED_TIME(T,'start');
  For I in 1..100 loop
    X := Sysdate;  -- Spend some time looping
  End loop;
  PK2.ELAPSED_TIME(T);
  DBMS_Output.Put_Line(T);
End;
.
/

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips