Home » SQL & PL/SQL » SQL & PL/SQL » dbms_lock.sleep(15) won't stop for 15 seconds
dbms_lock.sleep(15) won't stop for 15 seconds [message #225794] Wed, 21 March 2007 09:20 Go to next message
jhs@nsp
Messages: 4
Registered: March 2007
Location: nova scotis
Junior Member
I want to make this script eventually run some sql every n seconds but

When I execute this script 'sleep.sql' in SQLPlus

--sleep.sql
SET ECHO ON;
BEGIN
LOOP
dbms_lock.sleep(15);
End loop;
END;
/


I would expect the script to pause for 15 seconds - but it runs right through to the end, then stops after line '/'. When I do a CTL+C to end it throws an error referring to SYS.DBMS_LOCK.
ORA-06512: at "SYS.DBMS_LOCK", line 201




Here's the output

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 21 11:05:00 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> start sleep.sql
SQL>
SQL> BEGIN
2 LOOP
3 dbms_lock.sleep(15);
4 End loop;
5 END;
6 /

^CBEGIN
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_LOCK", line 201
ORA-06512: at line 3



SQL>

Thanks, very new to Oracle...

Jamie
Re: dbms_lock.sleep(15) won't stop for 15 seconds [message #225796 is a reply to message #225794] Wed, 21 March 2007 09:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Well, you are looping without an exit. So the loop will go on forever, unless you cancel it. Within each cycle, you invoke dbms_lock.sleep.

MHE
Re: dbms_lock.sleep(15) won't stop for 15 seconds [message #225798 is a reply to message #225796] Wed, 21 March 2007 09:32 Go to previous messageGo to next message
jhs@nsp
Messages: 4
Registered: March 2007
Location: nova scotis
Junior Member

Actually it doesn't loop at all, just blows right through to the end.

Ya I took the exit out - just to show code in question, had this in
...
SELECT x into rslt
from looptest;
EXIT when rslt = 'YES';


But still it doesn't loop at all, just blows right through to the end.
Re: dbms_lock.sleep(15) won't stop for 15 seconds [message #225806 is a reply to message #225794] Wed, 21 March 2007 09:59 Go to previous messageGo to next message
jhs@nsp
Messages: 4
Registered: March 2007
Location: nova scotis
Junior Member
Ah hold off I think thatthe sleep is working, it seems that sqlplus echos the entire script in one big dump - I thought it might wait at the dbms sleep line for 15...then go to the next line...
Re: dbms_lock.sleep(15) won't stop for 15 seconds [message #225809 is a reply to message #225806] Wed, 21 March 2007 10:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Indeed. The pl/sql block is parsed and executed as a whole.

MHE
Re: dbms_lock.sleep(15) won't stop for 15 seconds [message #225811 is a reply to message #225809] Wed, 21 March 2007 10:11 Go to previous message
jhs@nsp
Messages: 4
Registered: March 2007
Location: nova scotis
Junior Member
I told you I was new to this!

Thanks for the help.

JS
Previous Topic: PL/SQL question
Next Topic: how to transfer data from sql-server to oracle ?
Goto Forum:
  


Current Time: Fri Dec 09 11:37:59 CST 2016

Total time taken to generate the page: 0.11672 seconds