CREATE OR REPLACE TYPE BODY timer_t AS CONSTRUCTOR FUNCTION timer_t (self IN OUT timer_t, title_in IN VARCHAR2) RETURN SELF AS RESULT IS BEGIN self.title := title_in; RETURN; END; MEMBER PROCEDURE start_timer IS BEGIN start_time := DBMS_UTILITY.get_time; END; MEMBER PROCEDURE show_elapsed_time IS BEGIN DBMS_OUTPUT. put_line ( 'Elapsed ' || 'for ' || self.title || ' = ' || TO_CHAR (
MOD (DBMS_UTILITY.get_time - start_time + POWER (2, 32) , POWER (2, 32))); END; END;A player wrote to me asking why I included two calls to "POWER(2,32)" - which made me realize that I had never explained that fairly odd-looking code. The general algorithm for using DBMS_UTILITY.get_time is to call it once before you start your code (start time) and then again when your code finishes (end time). Subtract start time from end time and you are left with the number of hundredths of seconds it took to run the code. So what's with the POWER calls? The integer returned by DBMS_UTILITY.get_time gets bigger and bigger until it hits an OS-specific limit. Then it set to 0 and starts incrementing again. If you call DBMS_UTILITY.get_time to get your start time just before this "roll over" occurs, you will end up with a very large negative elapsed time (impossible!). So instead, I take the difference, add a really big number to it (bigger than the rollover point for operating system), then mod by that number. In this way, I am guaranteed a positive elapsed time. I like to use this story as an example of how even the most trivial-seeming formula will end up being more complex than you originally expected. Which is why you must always hide rules and formulas behind functions.