PL/SQL Delay

I had an app to demonstrate where I had built an autonomous scheduled status checker with customer communication generated on certain status changes. This is a very difficult thing to demonstrate without letting the 24-hour cycle run its course. Unfortunately, people get bored of waiting for status changes and want you to speed things up.

To accommodate this, I wanted to “fake” the schedule by making the scheduled jobs run one after another with a minute or two gaps, but at the same time, I wanted it all to be within one PL/SQL block rather than individual jobs.

This is where I found the DBMS_LOCK.SLEEP command!

Use it in this really simple format:

begin
   DBMS_LOCK.SLEEP(seconds);
end;

This will freeze the procedure where it is for as many seconds as you have input.

Read more here: DBMS_LOCK.SLEEP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s