Output Time Difference

[timmbo]

Hi All,

Attempting to calculate the time difference on a procedure for Start and End time.

PROCEDURE... IS

  v_myTime1        VARCHAR2(9);
  v_myTime2        VARCHAR2(9);
  v_myTimeResult   VARCHAR2(9);
 
CURSOR...

  BEGIN

  --Start time.
  SELECT TO_CHAR(sysdate, 'HH24:MI:SS') INTO v_myTime1 FROM dual;
  DBMS_OUTPUT.PUT_LINE('Started: ' || v_myTime1);
  OPEN
    LOOP
       etc...
    END LOOP

   --Finish time.
   SELECT TO_CHAR(sysdate, 'HH24:MI:SS') INTO v_myTime2 FROM dual;
   DBMS_OUTPUT.PUT_LINE('Completed: ' || v_myTime2);

   --Determine time difference.
   SELECT TO_CHAR((TO_DATE(v_myTime1, 'HH24:MI:SS') - TO_DATE(v_myTime2, 'HH24:MI:SS'))) 
	   INTO v_myTimeResult FROM dual;
   DBMS_OUTPUT.PUT_LINE('Time Difference: ' || v_myTimeResult);
END;

Variables v_myTime1 and v_myTime2 work fine and are printed.
But, I can't get v_myTimeResult to print. Can anyone help me here?

TIA,
Tim

 

[Hehenka]

You can try this:

--Determine time difference.
v_myTimeResult:= TO_CHAR((TO_DATE(v_myTime1, 'HH24:MI:SS') - TO_DATE(v_myTime2, 'HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE('Time Difference: ' || v_myTimeResult);

 

[lewisp]

You could use DBMS_UTILITY.Get_Time. This returns a number that increments every 100th of a second. Hit it at the start and end of your procedure, subtract the start from the end and you have the number of 100ths of a second it took your procedure to run.

 

[timmbo]

Thanks all for the response. Have a good one.