Don't judge by it's order in the object tree!

DBMS_SCHEDULER is awesome. It’s flexibility is remarkable. From setting up execution windows to remote execution to automatic notifications, there is a LOT to like. That doesn’t mean it’s perfect. For developers and DBAs, it can be a real pain to type out 2000+ characters to simply create a job. Being able to have such a fine level of control over the execution of a job has led to a very complex mechanism and infrastructure. This complexity, however, really sets the scene for a GUI – a nice visual interface that allows the user to input some information, which then churns out the nasty-looking code for you.

Of course if you enjoy using EM or Grid Control and you’re already there, feel free to use the web forms. But, if you already have SQL Developer spun up, you might as well do it there. I personally think our implementation is better, but please don’t tell anyone I said that ;)

If you haven’t noticed this feature in SQL Developer, I forgive you. It’s at the very end of a very long tree list. If you connect to your database and expand a SCHEMA, you’ll see ‘SCHEDULER’ at the very bottom. In this case, it’s worth the scroll!

There’s a wizard interface that will walk you through the entire process of creating a new job, or you can use the traditional forms to create and alter existing jobs and schedules.

Let’s take a quick look. And by quick, I mean, let’s zoom into one very small aspect of working with the SCHEDULER and SQL Developer -

Defining repeating intervals for your job to execute

The mechanism for defining when your job will execute is nearly identical to setting up a schedule. It’s lurking under the covers. Look for the ‘edit’ button in the job and schedule forms – a la

Clicking the edit button will bring you to a form that looks something like this:

Quickly build your Repeating Interval definition

This form is fairly self-explanatory, but a couple of notes:

  1. No worries, if you get frustrated with the form at some point, check the Advanced box and manually code your interval
  2. Check this to indicate last versus first occurrence, e.g. -1 would be the last day of the month. Handy as each month has a different number of days!

I know some of you will still insist on doing everything by the command-line

And that’s OK (grudging acceptance), but at least spin up SQL Developer to create your scripts. The beauty of these dialogs is that they show the underlying SQL statements so you can easily copy them into your .SQL files.