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

610 pts.
PL/SQL block
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

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:

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
— try your select/insert transaction here
if <ok> then
g_sleep_ctr := 0; — terminate loop
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.
  • Inprise
    Thanks it answered my question
    610 pointsBadges:

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.

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


Share this item with your network: