I was remarking to someone this morning how easy I thought it was to setup a job, say on the first and third Wednesday of the month. The flexibility of DBMS_SCHEDULER, even without taking into account the power of schedules, is really powerful.

We have a scheduler wizard that puts a graphical user interface onto the DBMS_SCHEDULER package.

Here’s how it works, in a decent sized nutshell.

The wizard has a lot more options and widgets...
The wizard has a lot more options and widgets…

Which will launch…

Don't mind the code - that's not the important part.
Don’t mind the code – that’s not the important part.

Clicking the edit button opens the frequency dialog.

You can add as many days as you want...
You can add as many days as you want…

From the DOCS, ‘ByDay’

This specifies the day of the week from Monday to Sunday in the form MON, TUE, and so on. Using numbers, you can specify the 26th Friday of the year, if using a YEARLY frequency, or the 4th THU of the month, using a MONTHLY frequency. Using the minus sign, you can say the second to last Friday of the month. For example, -1 FRI is the last Friday of the month.

We also have calendar pickers for setting specific days and times, say for example the start and end date for the JOB to run.

The time will default to your local current time...
The time will default to your local current time…

The wizard lets you setup much more, including mail notifications, but I’m going to jump to the end.

We ALWAYS make the SQL or PL/SQL available for review.
We ALWAYS make the SQL or PL/SQL available for review.

Why would you want to type all of that? And this is just a simple example job, they will usually be much more complicated.

I’m not going to talk about the job editors, but they are very nice too – especially when you want to look at scheduler chains…

So, I went to run my job a few times and noticed I threw in the ‘/’ in the ANON BLOCK code – bad boy! So I edited the job, and then ran it a couple of times.

Opened the job editor, split it 3 times, and got a nice, all in one view of pretty much everything you’d need to know.

You know how to split editors, right?
You know how to split editors, right?
thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

Write A Comment