How to implement Sleep logic not using sleep() need customized

610 pts.
Tags:
DBMS_LOCK.SLEEP
PL/SQL
PL/SQL block
SLEEP Procedure
Hi Experts, I need to implement a sleep logic in a loop i need to check for SELECT/INSERT stm if it s locked by other user i will go to sleep mode then back to earlier savepoint position eg savepoint x; INSERT/SELECT i need to check for 10 times each time sleep is for 1 sec i applied counter i tried the logic WHILE g_savepoint_loop <= 3 LOOP g_sleep := '1' ; g_sleep := g_sleep_in_sec + 1 ; END LOOP; ROLLBACK TO x ; By query here is how to declare g_sleep to 1 sec(datatype ??) ,my logic says wrong type please guide me
ASKED: December 16, 2008  12:45 PM
UPDATED: December 18, 2008  8:52 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

In your example, you have a statement assigning a character value to what should be a number:

g_sleep := ’1′ ; — this assigns g_sleep a string value ’1′, instead of a number value

This may be the error, or if you declared the variable g_sleep as a character/varchar type, then the next statement would fail:

g_sleep := g_sleep_in_sec + 1 ;
This statement has a couple of weird things… In your loop, it is going to take the “g_sleep_in_sec” value (whatever that happens to be), add 1 to it, and assign it to g_sleep. If g_sleep_in_sec is less than 2, the loop will never terminate.

In Oracle, just use the DBMS_LOCK package Sleep procedure:

DBMS_LOCK.SLEEP (
seconds IN NUMBER);

You can sleep almost any amount of time – you can even use fractional values to sleep for time periods less than a second.

DBMS_LOCK.SLEEP(1); — sleep for one second
DBMS_LOCK.SLEEP(60); — sleep for one minute
DBMS_LOCK.SLEEP(0.05); — sleep for 50 milliseconds
DBMS_LOCK.SLEEP(0.001); — sleep for 1 millisecond

So, to test and sleep,

g_sleep_ctr number;
gc_sleep_time number;

g_sleep_ctr := 3;
g_sleep_time := 1.0;

while g_sleep_ctr > 0 — I strongly prefer counting down to zero
— so loops always terminate when the loop variable is 0 or less
loop
— try your select/insert transaction here
if <ok> then
g_sleep_ctr := 0; — terminate loop
else
dbms_lock.sleep(g_sleep_time);
g_sleep_ctr := g_sleep_ctr – 1;
end if;
end loop;

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Inprise
    Thanks it answered my question
    610 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following