Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Stanley.

Asked: January 05, 2002 - 12:29 pm UTC

Last updated: April 25, 2007 - 1:27 pm UTC

Version: 9i

Viewed 10K+ times! This question is

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 Tom said...

Its OK, as it's only useful for measuring elapsed time.

ops$tkyte@ORA9I.WORLD> exec dbms_output.put_line( dbms_utility.get_time );
-2051479853

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> variable n number
ops$tkyte@ORA9I.WORLD> exec :n := dbms_utility.get_time

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> exec dbms_lock.sleep( 1 )

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> 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)

ops$tkyte@ORA806.WORLD> exec dbms_output.put_line( dbms_utility.get_time );
-2051457232

PL/SQL procedure successfully completed.

ops$tkyte@ORA806.WORLD> variable n number
ops$tkyte@ORA806.WORLD> exec :n := dbms_utility.get_time

PL/SQL procedure successfully completed.

ops$tkyte@ORA806.WORLD> exec dbms_lock.sleep( 1 )

PL/SQL procedure successfully completed.

ops$tkyte@ORA806.WORLD> exec dbms_output.put_line( dbms_utility.get_time - :n );
101

PL/SQL procedure successfully completed.

ops$tkyte@ORA806.WORLD>
ops$tkyte@ORA806.WORLD>
ops$tkyte@ORA806.WORLD>
ops$tkyte@ORA806.WORLD> exec dbms_output.put_line( dbms_utility.get_time+power(2,32));
2243510166

PL/SQL procedure successfully completed.

ops$tkyte@ORA806.WORLD> variable n number
ops$tkyte@ORA806.WORLD> exec :n := dbms_utility.get_time+power(2,32)

PL/SQL procedure successfully completed.

ops$tkyte@ORA806.WORLD> exec dbms_lock.sleep( 1 )

PL/SQL procedure successfully completed.

ops$tkyte@ORA806.WORLD> 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)

Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DBMS_UTILITY.GET_TIME", version 9i

D.Walsh, December 05, 2002 - 1:15 pm UTC

I have Oracle 9i and it seems as though dbms_lock.sleep( 1) is not a functionality. I receive the error:

SQL> exec dbms_lock.sleep( 1 );

begin dbms_lock.sleep( 1 ); end;

ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOCK' must be declared 

Tom Kyte
December 05, 2002 - 3:06 pm UTC

you need to have execute on dbms_lock granted to you -- see your dba to see if that is possible.

dbms_lock

Anuj, December 10, 2003 - 1:20 pm UTC

Tom!
Thanks in advance .Well i have to get the data from table(another database) after its updated by some process (say process a).I can figure out ,if a is complete that using decode statement below if it returns flag 'YES' then i can start my process (say b) ,but when flag is 'NO' i have to keep checking for this condition evey 10 minutes if flag is 'YES'.I can not use dbms_job for some reason .Pl advise can if i can use dbms_lock to achieve this or there is some other neat(your) way of doing this .

PROCEDURE proc1 IS

lv_flag Varchar2(3);
lv_run_date DATE;

BEGIN
BEGIN

SELECT DECODE(TL_STATUS,'TL COMPLETE',DECODE(LTL_STATUS,'LTL COMPLETE','YES','NO'),'NO')
INTO lv_flag FROM source_table
WHERE ID = -999; --condition to see other process is complete before i start my -- process

SELECT max(etl_date) +1+6/24 INTO lv_run_date FROM target_table ;

IF(lv_flag ='NO' AND (lv_run_date > lv_date OR lv_run_date IS NULL)) THEN
dbms_lock.sleep(600);

proc1;


ELSIF(lv_flag ='YES')THEN

--do insert into target table

....................
....................
....................
end proc1 ;

Tom Kyte
December 10, 2003 - 4:09 pm UTC

you are using dbms_lock already?

dbms_lock.sleep

Anuj, December 10, 2003 - 4:33 pm UTC

Hi Tom,
Thats what i'm trying to figure out if there is any better and effcient way to do the same. If proc keeps calling itself for long hours say process a fails then how it will impact db .

Thanks & Regards


Tom Kyte
December 10, 2003 - 4:43 pm UTC

dbms_lock.sleep puts you to sleep, you are blocked, just hanging out, not doing anything.

you are OK as is

how to find out milli secs..

A reader, October 11, 2006 - 4:01 pm UTC

Hi tom,

Does oracle have something like

//java code ..
Calendar cal = Calendar.getInstance();
cal.set(2006, 8, 01, 0, 0, 0) ;
cal.getTimeInMillis();

returns milli seconds in long since January 1, 1970, 00:00:00 GMT.

how to get same # in oracle 10g R2 ? possible ?

TIA

Tom Kyte
October 11, 2006 - 8:10 pm UTC

to_timestamp the time you want, subtract that from systimestamp

how to do it ?

A reader, October 12, 2006 - 12:37 pm UTC

Tom when i ran following (10g R2)

SELECT TO_TIMESTAMP ('01-Sep-2006')
FROM dual
I got back
9/1/2020 6:00:00.000000000 AM

SELECT TO_TIMESTAMP ('01-Sep-2006') - SYSTIMESTAMP FROM DUAL
+5072 17:31:02.185014

Thi is nowhere near long # which is returnted by the java object.

Please help

Tom Kyte
October 13, 2006 - 6:50 am UTC

you'll have to convert that into milliseconds - you have all of the bits you need, now it is just "math"


ops$tkyte%ORA10GR2> select i,
  2         substr(i,1,instr(i,' ')-1) dys,
  3         substr(i,instr(i,' ')+1,instr(i,':')-instr(i,' ')-1 ) hh,
  4         substr(i,instr(i,':')+1,instr(i,':',1,2)-instr(i,':')-1 ) mm,
  5         substr(i,instr(i,':',1,2)+1) ss
  6    from (
  7  SELECT systimestamp-TO_TIMESTAMP ('01-jan-1970','dd-mon-yyyy') i
  8    FROM DUAL
  9         )
 10  /

I
---------------------------------------------------------------------------
DYS                            HH
------------------------------ ------------------------------
MM                             SS
------------------------------ ------------------------------
+000013434 06:39:44.407201000
+000013434                     06
39                             44.407201000



 

this is what I need.

A reader, October 12, 2006 - 12:40 pm UTC

I want to convert (using select) '01-Sep-2006 00:00:00' GMT date

to milliseconds from Jan 1,1970 GMT.

some thing like 1157083200000

To "A reader"

Michel Cadot, October 13, 2006 - 7:31 am UTC

Something like:

SQL> alter session set nls_date_language=american;

Session altered.

SQL> var theDate varchar2(30);
SQL> exec :theDate := '01-Sep-2006 00:00:00';

PL/SQL procedure successfully completed.

SQL> select to_number(to_char(to_date(:theDate,'DD-Mon-YYYY HH24:MI:SS'),'J'))*86400 +
  2         to_number(to_char(to_date(:theDate,'DD-Mon-YYYY HH24:MI:SS'),'SSSSS')) -
  3         (to_number(to_char(to_date('01/01/1970','DD/MM/YYYY'),'J'))*86400+
  4          to_number(to_char(to_date('01/01/1970','DD/MM/YYYY'),'SSSSS'))) sec
  5  from dual;
       SEC
----------
1157068800

1 row selected.

Regards
Michel
 

Thanks guys

A reader, October 13, 2006 - 11:40 am UTC

Thanks, guys.


but, if you convert this # back to date it gives
some date in 2004 and not the same date



Tom Kyte
October 13, 2006 - 2:36 pm UTC

and just how did you ..... do this conversion?

We are from Missouri, the show me state here...

To "A reader"

Michel Cadot, October 13, 2006 - 2:48 pm UTC

SQL> var sec number;
SQL> exec :sec := 1157068800;

PL/SQL procedure successfully completed.

SQL> select to_char(to_date('01/01/1970','DD/MM/YYYY')+:sec/86400, 
  2                 'DD-Mon-YYYY HH24:MI:SS')
  3  from dual;
TO_CHAR(TO_DATE('01/
--------------------
01-Sep-2006 00:00:00

1 row selected.

Michel
 

Thanks

A reader, October 13, 2006 - 5:24 pm UTC


Thanks to both of you. it worked !!







how about this ...

A reader, October 13, 2006 - 6:33 pm UTC


CREATE OR REPLACE FUNCTION toEpochTime (dt IN DATE)
RETURN NUMBER
IS
epochStartDt DATE :=TO_DATE ('01-JAN-1970 00:00:00', 'dd-mon-yyyy hh24:mi:ss');
BEGIN
;
RETURN ((dt - epochStartDt) * 24 * 60 * 60 * 1000);
END toEpochTime;


Utility to Convert DBMS_UTILITY.GET_TIME output to hours, minutes, etc

Dave, February 21, 2007 - 5:15 pm UTC

Was wondering if Oracle supplies a companion utility to DBMS_UTILITY.GET_TIME that converts the output into hours, minutes, seconds and fractions of seconds. We have written a series of statements and created our own function:

declare
n_Elapsed_Time pls_integer := 567890;
fraction pls_integer;
seconds pls_integer;
minutes pls_integer;
hours pls_integer;
begin
-- Get the fractions of seconds
fraction := MOD(n_Elapsed_Time, 100);
n_Elapsed_Time := n_Elapsed_Time / 100;

-- Get the seconds
seconds := MOD(n_Elapsed_Time, 60);
n_Elapsed_Time := n_Elapsed_Time / 60;

-- Get the minutes
minutes := MOD(n_Elapsed_Time, 60);

-- Get the hours
hours := TRUNC(n_Elapsed_Time / 60);
end;
Tom Kyte
February 21, 2007 - 7:34 pm UTC

not really, it is just "hundredths of seconds", it is just math from there to display however you want...

Alexander the ok, April 25, 2007 - 1:05 pm UTC

Hi Tom, I have a dumb question, sorry.

I'm timing a script, I want this at the end

SQL> exec dbms_output.put_line('Total time was: ' || to_char(round(22.111111111, 2)) || ' minutes');
Total time was: 22.11 minutes

PL/SQL procedure successfully completed.


(:n being the start time)

SQL> exec dbms_output.put_line('Total time was: ' || to_char( round(dbms_utility.get_time+power(2,32)- :n)/100/60, 2) || ' minutes');
BEGIN dbms_output.put_line('Total time was: ' || to_char( round(dbms_utility.get_time+power(2,32)- :n)/100/60, 2) || ' minutes'); END;

*
ERROR at line 1:
ORA-01481: invalid number format model
ORA-06512: at line 1


I can't figure out what I'm doing wrong. Thanks
Tom Kyte
April 25, 2007 - 1:27 pm UTC

the way your () are right now, you have:

to_char( expression )

where expression is:
round(dbms_utility.get_time+power(2,32)- :n)/100/60, 2)

it is saying the format "2" is wrong.

simple () muck up on your part, they are nested wrong

This is what i did to calculate time in milliseconds

sony, March 12, 2015 - 7:26 pm UTC

select to_char(systimestamp(6),'RRRRMMDDHH24MISSFF6') from dual;

www.sqlqueryexamples.com

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library