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
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 ;
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
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
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
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
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;
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 1I can't figure out what I'm doing wrong. Thanks
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