There’s a nice little “sleep” procedure in Oracle: A procedure that stops the execution of the current thread for n seconds.
Strangely, this method can be called in SQL*Plus like so:
but not in another stored procedure or function like so
CREATE OR REPLACE PROCEDURE FOOBAR AS BEGIN DBMS_LOCK.SLEEP(1); END; /
To use “sleep” in your procedures or functions, login as administrator to your database (or ask you admin to to so) and create the following objects:
CREATE OR REPLACE PROCEDURE sleep(seconds NUMBER) AS BEGIN DBMS_LOCK.SLEEP(seconds); END; / CREATE OR REPLACE public synonym sleep FOR sleep; GRANT EXECUTE ON sleep TO public;
and in your procedure just use “sleep”.
Man you saved my day! Had exactly that darn problem and couldn’t figure out why. Thanks a lot! 🙂
You’re welcome, Marc.
Nice Blog, btw!
You can call it from a procedure. I suspect you have execute on dbms_lock through a role, but to call the procedure the permission needs to be granted directly to the user.
It seems that in order to use DBMS_LOCK you need to be granted EXECUTE on DBMS_LOCK by SYS (or SYSTEM?). Wht the package is just not publicly available out-of-the-box is unknown. No idea what is so terrible in the package.
Exactly that didn’t work for me. I had to create this superfluous alias, for whatever reason. You see i have grant on this as well.
This is very useful, thnx for sharing this information.
You’re welcome Muneer.
Hi, I created a procedure like this.
create or replace procedure proc_sleep
Generally, it should first print start then go off to sleep for 10 seconds and then it should print end. However when I run this, it first goes off to sleep for 10 seconds and then it prints start and end. Can you please let me know the reason behind this and how to resolve it. I am using SQL developer and oracle 10g express edition.
your procedure is correct.
PL/SQL isn’t SQL. So if you call this, context switches to PL/SQL. dbms_output writes to a buffer and that buffer is printed when the context switches back, that is, after your proc ends. See also “Operational Notes” in the docs http://docs.oracle.com/cd/B193.....m#i1000634 or this answer on Stackoverflow: http://stackoverflow.com/a/1472673
Thanks! this helped me. I wrote a procedure with an if condition and then made it sleep and then i again had a if condition. Avoided the dbms_output and it worked properly. I have another query.
In a procedure, I have created a sequence at the start of the procedure. Now in the end of my procedure, I want to reset the value of the sequence to 1. Tried using execute immediate but it works individually but not when I invoke it in my procedure. Can you please let me know any way of resetting my sequence?
I’d probably try this http://stackoverflow.com/a/93633/1547989
Hi Michale, yes this link did help. Now I am able to reset the sequence. Thanks once again!! Now, a further query. Can you please let me know how to check the diskspace by running a query in a procedure? In one project I have to check the diskspace(in percentage). A link would also do.
Let me google that for you 😉
Hey Michael, thanks for this one; was a little busy with some another projects hence it took me long to have a look at this one. This query also worked and thanks for this as well. I have another query. My string can store max of 4000 characters, however I have approx 5000 characters to store. When I try to store it, it gives me an error saying that it is more than 4000 characters hence the data cannot be stored. Is it possible to store 4000 characters out of that 5000 characters without an error? I m ok if the remaining 1000 characters are not inserted!
I still work with my stored procedure, but it has dbms lock sleep in order to make 2 stored procedures meet at the same time. I want to implements management transaction within the store procedure. The problem is when I executed the store procedure with dbms lock sleep, it won’t work and error : dbms lock sleep must be declared.
Hi, see Novita, please read the post again. Create a delegating procedure as admin and a public synonym for that. Also in Comment #4.
Could you please help me in writing a procedure which will create two jobs in such a way that when the first job gets completed then the second job is created. the second job will not get created until and unless first job gets completed.
to use DBMS_LOCK.sleep in a procedure/function you need just to have correct definition of AUTHID and you can execute it in an anonymous block.
CREATE OR REPLACE PROCEDURE someproc
…some other stuff…
EXECUTE IMMEDIATE ‘BEGIN DBMS_LOCK.sleep(3); END;’;
…some other stuff…