Stanley -- Thanks for the question regarding "DBMS_UTILITY.GET_TIME", version 9i
Submitted on 5-Jan-2002 12:29 Eastern US timeTom's latest followup | Bookmark | BottomLast updated 25-Apr-2007 13:27
You Asked
Hi Tom,
Earlier in Oracle 7.3.4 the return value from DBMS_UTILITY.GET_TIME was positive
number but when i tried in Oracle 9i am getting a negative value.
SQL>ed gettime
Set Serveroutput On
Declare
nTemp Number(12);
Begin
nTemp := Dbms_Utility.Get_Time;
Dbms_Output.Put_Line(nTemp);
End;
/
SQL> sta gettime
-2065910589
Is this how the return value should be or is there any problem?
Thanks in Advance
Regards
Stanley
and we said...
Its OK, as it's only useful for measuring elapsed time.
[email protected]> exec dbms_output.put_line( dbms_utility.get_time );
-2051479853
PL/SQL procedure successfully completed.
[email protected]> variable n number
[email protected]> exec :n := dbms_utility.get_time
PL/SQL procedure successfully completed.
[email protected]> exec dbms_lock.sleep( 1 )
PL/SQL procedure successfully completed.
[email protected]> exec dbms_output.put_line( dbms_utility.get_time - :n );
108
PL/SQL procedure successfully completed.
so it still works in that capacity.
If you don't like it being "negative" (its a function of the machine clock and CAN be
observed in 8/7.x as well), you can add power(2,32) to the number.Here is an example
from 806 showing it can (and will) be negative (it'll EVENTUALLY go positive again, we
put it into a 32 bit signed integer, it rolls over, goes negative then goes to zero and
goes back up)
[email protected]> exec dbms_output.put_line( dbms_utility.get_time );
-2051457232
PL/SQL procedure successfully completed.
[email protected]> variable n number
[email protected]> exec :n := dbms_utility.get_time
PL/SQL procedure successfully completed.
[email protected]> exec dbms_lock.sleep( 1 )
PL/SQL procedure successfully completed.
[email protected]> exec dbms_output.put_line( dbms_utility.get_time - :n );
101
PL/SQL procedure successfully completed.
[email protected]>
[email protected]>
[email protected]>
[email protected]> exec dbms_output.put_line( dbms_utility.get_time+power(2,32));
2243510166
PL/SQL procedure successfully completed.
[email protected]> variable n number
[email protected]> exec :n := dbms_utility.get_time+power(2,32)
PL/SQL procedure successfully completed.
[email protected]> exec dbms_lock.sleep( 1 )
PL/SQL procedure successfully completed.
[email protected]> exec dbms_output.put_line( (dbms_utility.get_time+power(2,32)) -
:n );
101
PL/SQL procedure successfully completed.
If you are UNLUCKY enough to hit it when it rolls- you'll get weird timings (been there,
done that) |