Oracle DBMS_SCHEDULER Part 2 – The API

In Part 1 of this series, I introduced you to the basic components and some information you need to know before using the scheduler. Today’s post will cover using the API to create some basic objects. I’ll show how to use SQL Developer to simplify some of these tasks.

Today’s post will cover creating programs and jobs.

Programs

As I said in my last scheduler post: it all starts with the program. A program is a PL/SQL procedure (can be packaged but not a function), PL/SQL block or external OS program.

You do not need to define a program if you don’t want to. A job can define a program with in its own declaration. I like to define programs as stand alone objects for several reasons. First, I can reuse them and schedule them with different frequencies (which I could honestly do with jobs but I would have to redescribe the program each time) and I can grant program access to others.

The API for creating a program is:

DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name             IN VARCHAR2,
   program_type             IN VARCHAR2,
   program_action           IN VARCHAR2,
   number_of_arguments      IN PLS_INTEGER DEFAULT 0,
   enabled                  IN BOOLEAN DEFAULT FALSE,
   comments                 IN VARCHAR2 DEFAULT NULL);

I’ll use a very simple stored procedure for my demonstration.

CREATE OR REPLACE PROCEDURE sample_scheduler_proc(
  first_parameter VARCHAR2,
  second_parameter NUMBER,
  special_parameter VARCHAR2 )
AS
BEGIN
  INSERT INTO logger_tab (msg) values (
      'fp: ' || first_parameter ||
      'sp: ' || second_parameter ||
      'special: ' || special_parameter );
END;

You can see that it is a procedure (not much reason to define an asynchronous function) and it has 3 parameters.

And now I define the program:

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name             => 'sample_prg',
    program_type             => 'STORED_PROCEDURE',
    program_action           => 'sample_scheduler_proc',
    number_of_arguments      => 3,
    enabled                  => FALSE,
    comments                 => 'Just some comments');
END;

There are three types of programs, STORED_PROCEDURE, PL/SQL block and EXECUTABLE. An executable is an operating system executable (and this topic will be the basis of a future post). A PL/SQL block is an anonymous block. It can take no parameters but can do anything an anonymous block can do (use this if you need to schedule a function, or better yet, wrap the function in a procedure and schedule that).

I have to define the program with enabled => FALSE until I identify the procedure’s parameters. There are 3 ways to define parameters: no default value, with a default value and as metadata. I will show an example of each below.

BEGIN
  -- no default value
  DBMS_SCHEDULER.define_program_argument(
    program_name            => 'sample_prg',
    argument_position       => 1,
    argument_name           => 'first_parameter',
    argument_type           => 'VARCHAR2',
    out_argument            => FALSE);

  -- with default value
  DBMS_SCHEDULER.define_program_argument(
    program_name            => 'sample_prg',
    argument_position       => 2,
    argument_name           => 'second_parameter',
    argument_type           => 'NUMBER',
    default_value           => 8,
    out_argument            => FALSE);

  -- metadata
  DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT (
    program_name            => 'sample_prg',
    metadata_attribute      => 'job_name',
    argument_position       => 3,
    argument_name           => 'special_parameter');

END;

The first two parameter definitions are pretty self explanatory. The first is a varchar parameter and the second is a numeric. The second one has a default value.

The third parameter, my special_parameter, is a meta data argument. The meta data comes from the scheduler and gives your job access to information about the job, the schedule, etc. In this case, the job name will be passed into my program. You can also pass in:

  • Job Subname – if a job called a job (or is in a chain), the job will have a subname
  • Job Owner – who owns the job
  • Job Scheduled Start – When was the job scheduled to start
  • Job Start – When did the job actually start
  • Window Start – What time the window opened (if windows were used)
  • Window End – What time the window is scheduled to close (if windows were used)
  • Event Message – The name of the event that started the job (if event based scheduling was used)

Then we enable the program.

BEGIN
  DBMS_SCHEDULER.ENABLE (
    name              => 'sample_prg');
END;

That’s it for the program. Now we’ll create a job and run it.

Jobs

There are many permutations of job: creating a job with inline program and no schedule, inline program and schedule, inline schedule and a program, program and event, etc. I’m not going to regurgitate each permutation here. For today’s post I will do a very basic job that uses our defined program and a run now inline schedule. In future posts, after we discuss schedules and events, I will provide examples of other, more complex configurations.

Here is the definition of (one of) the create_job API calls. I will leave out unneeded parameters in my call.

DBMS_SCHEDULER.CREATE_JOB (
   job_name             IN VARCHAR2,
   program_name         IN VARCHAR2,
   start_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   repeat_interval      IN VARCHAR2                 DEFAULT NULL,
   end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
   enabled              IN BOOLEAN                  DEFAULT FALSE,
   auto_drop            IN BOOLEAN                  DEFAULT TRUE,
   comments             IN VARCHAR2                 DEFAULT NULL,
   job_style            IN VARCHAR2                 DEFAULT 'REGULAR',
   credential_name      IN VARCHAR2                 DEFAULT NULL,
   destination_name     IN VARCHAR2                 DEFAULT NULL);

And here is my actual job creation.

BEGIN

  -- create the job, enabled false
  DBMS_SCHEDULER.CREATE_JOB (
    job_name             => 'MyJob',
    program_name         => 'sample_prg',
    start_date           => NULL,
    repeat_interval      => NULL,
    enabled              => FALSE,
    auto_drop            => TRUE);

  -- set the non-default parameter
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
    job_name             => 'MyJob',
    argument_name        => 'first_parameter',
    argument_value       => 'What up?');

  -- enable it and autorun it
  DBMS_SCHEDULER.ENABLE (
    name              => 'MyJob');    

END;

The first step creates the job. Enabled is false so that I can add my job argument for the first, non-default argument. Auto_drop is true. That means that once the job is run, Oracle will automatically remove its metadata from the list of jobs. That way Oracle can keep the list of jobs clean. If you plan to rerun the job, or schedule it regularly, set auto_drop to false.

The second step sets my parameter value. I need to pass some kind of a value in so I set the value here.

The third step enables my job. Since there is no schedule included (I left those values null by default), Oracle runs the job immediately. Since auto_drop is true, the job is removed after running.

I can query my logger_tab table (that’s what my demo proc was inserting into) and I get this:

fp: What up?sp: 8special: MYJOB

Not pretty but it is what I expected.

Something to notice is that all of the DBMS_SCHEDULER APIs, the create program, create job, etc. all commit when running. Unlike DBMS_JOB that required you to commit, DBMS_SCHEDULER commits automatically. Also, the procs that run (like my proc above) autocommit after running. That behavior is like DBMS_JOB.

SQL Developer (3+)

SQL Developer, Version 3 and higher, has added DBMS_SCHEDULER support. It has added a new graphical tool for dealing with job chains (which I will cover in depth in a future post) but it also has object level support in the navigator.

SQL Developer DBMS_SCHEDULER Support

Creating a program is simple. Right click on the program node and select new program. One issue that I have found though is that if you want to define a metadata argument, you have to resort to SQL. Still, SQL Developer makes that easy as it gives you the SQL when you create an object.

SQL Developer DBMS_SCHEDULER Support

And here is the SQL code from the SQL tab. You could grab this SQL and change the third argument to be a meta data argument.

SQL Developer DBMS_SCHEDULER Support

NOTE: I guess I should point out here that I am using a throwaway database and I am using the system schema to create my objects. That would be a bad thing to do in a normal database. Don’t do that.

Here is the screen to create the job. Right click on the job node and select new job.

SQL Developer DBMS_SCHEDULER Support

And here is the SQL. I always snag the SQL so that I can recreate objects easier in the future. I just rerun the scripts I saved off.

SQL Developer DBMS_SCHEDULER Support

That’s it for programs and jobs. I’ll be doing schedules and job chains in the near future.

LewisC

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

1 Comment »