| dbms_lock.sleep(15) won't stop for 15 seconds [message #225794] |
Wed, 21 March 2007 09:20  |
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 #225798 is a reply to message #225796] |
Wed, 21 March 2007 09:32  |
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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
