Mastering Oracle Scheduler in Oracle 11g Databases

The simplest type of job is the one that runs a PL/SQL block as shown here. A PL/SQL block as a job action is just that—a normal anonymous block of PL/SQL code. The advantage of this type is that it is very simple to set up as it does not need anything else in the database to be defined. As we cannot give any arguments to a PL/SQL block, we cannot use arguments for this type of job—something to remember when you try to create a library of reusable code.

Once we are successfully connected as MARVIN, the DB Console shows us the database Home page. From here, we select the Server tab to reach the page presented in the following screenshot. We are interested in the Oracle Scheduler column. All the implementations towards using the jobs are done here. We can do a lot of things in DB Console. However, for now we will restrict ourselves to making a Job. Click on the Jobs entry in the Oracle Scheduler column as shown here:

Click on the Jobs entry in the Oracle Scheduler column. In the screen that follows, we can see which jobs are already defined—or better, jobs that are yours or the jobs on which you have privileges:

Apparently, marvin doesn't own jobs and has no privileges at all to run any job. We are going to change this quickly. To do so, click on the Create button to bring us to the Create Job screen where most of the job definition is handled.

It shows us a few of the job properties that can be entered, selected, or switched as follows:

Here we can enter the properties of the job such as Name, Command Type of the job, and the action of the job. We can see what is entered. Because the default job type is of type PL/SQL block, we did not have to change anything for job type to get this going. Other attributes to note are the Enabled state and Logging Level.

If we create a job that has no other thing defined on it that regulates the execution of the job, such as a schedule or a window, the job will start running as soon as it is successfully created.

In the PL/SQL block, we can enter whatever code we want, as long as it is valid PL/SQL. In this case, there are a few simple inserts in a table.

Click on the Show SQL button. It will show the following code:

BEGIN
sys.dbms_scheduler.create_job
(
job_name => '"MARVIN"."TEST01"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
insert into session_log select * from v$session where sid = (select sid from v$mystat where rownum = 1);
insert into session_stat_log select * from v$mystat;
end;',
start_date => systimestamp at time zone 'Europe/Amsterdam',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'a simple test',
auto_drop => FALSE,
enabled => TRUE
);
sys.dbms_scheduler.set_attribute
(
name => '"MARVIN"."TEST01"',
attribute => 'logging_level',
value => DBMS_SCHEDULER.LOGGING_FULL
);
sys.dbms_scheduler.enable( '"MARVIN"."TEST01"' );
END;

Now, if we go back to the Create Job screen and click on the OK button, the job will be created and we will again come to the Scheduler Jobs screen. This time the list is no longer empty. It not only shows us our precious little job, but it also shows us that the job is in the RUNNING state as shown in the following screenshot:

In case you do not want a job to start running directly upon creation, create it in the disabled state. In this case, we created the job in the Enabled state and so the job starts running automatically. To run it once more, just click on the Run Now button and off it goes. All this is done without having to use a command-line tool. In the rest of the book, all examples will be using good old SQL*Plus, SQL Developer, or DbVisualizer. The choice among them is mostly influenced by the contents of my glass!

The next simplest type of job is the one that executes a stored procedure. A stored procedure is a piece of PL/SQL code that is stored in the database. It's not that this is more difficult; it's merely a question of creating the procedure and selecting it as job_action. In order to be able to select the procedure, we have to get the privileges to create the procedure. We can also select a procedure from another schema to use that in a job. In that case, we need the execute privilege on that procedure. However, we will create our own procedure. To be able to do that, grant marvin the correct privilege:

Grant create procedure to marvin;

And create a procedure called SNAP. It does not do much; it has the same contents as the PL/SQL block in the previous TEST01 job:

CREATE OR REPLACE PROCEDURE SNAP as
begin
insert into session_log select * from v$session where sid = (select sid from v$mystat where rownum = 1);
insert into session_stat_log select * from v$mystat;
end SNAP;

In DB Console, we need to change the Command Type by clicking on the Change Command Type button as shown in the following screenshot:

This brings us to the next screen where we can select the appropriate job type. In this case, it is Stored Procedure as shown in the following screenshot:

We can use the torch icon to select the procedure we want to run by the job as shown in the following screenshot:

Here, we select the SNAP procedure from MARVIN.

After clicking on the Select button from near the bottom of the screen, we get back to where we started from—that is, our selection—but this time with the procedure name entered as shown in the following screenshot:

Clicking on the OK button brings us back to the main job creation screen as shown in the following screenshot:

Using the Show SQL button will reveal the code that is generated for us:

BEGIN
sys.dbms_scheduler9.create_job
(
job_name => '"MARVIN"."TEST02"',
job_type => 'STORED_PROCEDURE',
job_action => '"MARVIN"."SNAP"',
start_date => systimestamp at time zone 'Europe/Amsterdam',
job_class => '"DEFAULT_JOB_CLASS"',
auto_drop => FALSE,
enabled => TRUE
);
END;

The job performs the same function as the previous example, but this time by calling a stored procedure that has to do the work. Sometimes, a job type PL/SQL is sufficient for the task. But if you are planning to use job arguments, you can't use a job type of PL/SQL. In that case, you will need a job type of stored procedure or program, which we will discuss later.

A job type that starts an executable is the last type that can be created in the DB Console without having to create another Scheduler object type first. As compared to the DB-only PL/SQL or stored procedure, an executable can be an operating system script or binary program which the user nobody has execution privileges on. As the user nobody is not exactly a very powerful user, this mostly means that the script or binary program has to be executable by everybody. As this kind of job performs code that is not in the database, we also call it an external job. Later in the book, we will discover the preferred alternative for external jobs, remote external jobs, which is introduced in Oracle 11g.

To be able to create external jobs, marvin needs the extra privilege.

Grant create external job to marvin;

The code could very well look like this:

BEGIN
sys.dbms_scheduler.create_job
(
job_name => '"MARVIN"."TEST03"',
job_type => 'EXECUTABLE',
job_action => '/tmp/testje.sh',
start_date => systimestamp at time zone 'Europe/Amsterdam',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'an external job test',
auto_drop => FALSE,
enabled => TRUE
);
END;

And the script testje.sh is here:

#!/bin/ksh
{
id
env
} >/tmp/testje.log 2>&1

This may not be the most complex script, but it does show some important things about how the job is run. Without this information, it's a bit hard to get a working script. As most of us know, jobs that are launched by cron have a simple environment. But jobs started by the Oracle Scheduler win the game when it comes to simplicity. The environment is almost empty:

uid=99(nobody) gid=99(nobody) groups=99(nobody) context=user_u:system_r:unconfined_t
_=/bin/env
PWD=/

The user who ran the script is by default the user nobody. The script that is started has slash (/) as working directory. So don't look surprised when you get hit by errors such as "permission denied". Another surprise might be that there is no job output shown in the Scheduler views—that is, if the job succeeds. Strangely enough, Oracle decided to show only job outputs in the Scheduler views when the job fails. The job gets a FAILED status when the error returned is other than 0. The error code is interpreted by Oracle using the regular errno.h, which we all know from the good old C language hacking days. So if you invent an exit code, Oracle will interpret it using the standard error codes. The good news is that the first few bytes of stderr are logged in the ADDITIONAL_INFO column of the *_scheduler_job_run_details view. The stderr is always logged, even when the error code is 0.

A program is a database object that can be used to build a library of building blocks to create jobs or job chains. Just like a job, a program can be chosen from a few types such as:

A program is not a runnable job by itself. It contains what a job or chain step should execute. It references the code and can easily be reused. When including programs in job steps, there are some limitations. But as long as we can do without arguments, we are OK. Let's create a few programs for the actions created above. When picking names, we need to think about the namespace where the jobs and programs live. They live in the same namespace. This means that if the TEST01 job exists, I cannot create a program called TEST01 as shown in the following screenshot. So, I will prefix the programs with P_.

The TEST01 program is prefixed by P_, which gives P_TEST01. Now, don't forget to enable the program. Unlike jobs, the programs are not started at the enable time. In this case, it is just made useable for jobs or chains. Also, create the P_TEST02 program that selects the stored procedure SNAP, and P_TEST03 that calls the executable.

A metadata argument is an argument that tells something about the currently running job.

There are several reasons for using metadata arguments in a program. One is that a program is executed by several different jobs and you want to know which job called the program. The job_name is one of the important metadata attributes for a metadata argument. The complete list of useable attributes is as shown in the following table:

This does look a bit mysterious, but a simple example will make things clearer. In this example, we make a small program that uses the job_name attribute to find out which job called the program.

First, give marvin the required privileges and quota to be able to create a log table:

Grant create table to marvin;

Alter user marvin quota unlimited on users;

Next, let marvin create the log table:

Create table log (job_name varchar2(30), dat_sys date);

Define a stored procedure that accepts one argument, the job_name:

--/
CREATE OR REPLACE PROCEDURE WHOCALLEDME (v_job varchar2) as
begin
insert into log (job_name, dat_sys) values (v_job, sysdate);
end WHOCALLEDME;
/

Now, create a program that uses this stored procedure:

--/
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => '"MARVIN"."P_CALLER"',
program_action => '"MARVIN"."WHOCALLEDME"',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
comments => 'show which job called me',
enabled => FALSE
);
end;
/

This program is created with enabled as FALSE. As not all of the arguments are defined, it cannot be enabled. Trying to run this code with enabled=> TRUE will result in an error message that will try to explain to us that not all arguments are defined. Now it is time to define the metadata argument to complete the job.

--/
begin
DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT
(
program_name => 'P_CALLER',
metadata_attribute => 'job_name',
argument_position => 1,
argument_name => 'v_job'
);
end;
/

The program is now completely defined with all of the arguments, so we can enable the program. If this program is called by some job, the Scheduler automatically inserts the correct values in the first argument of this program.

--/
BEGIN
dbms_scheduler.enable('P_CALLER');
END;
/

Now create the job that uses the enabled program, P_CALLER. The job name is test_m_arg and is defined as follows:

--/
BEGIN
dbms_scheduler.create_job
(
job_name => 'TEST_M_ARG',
program_name => 'P_CALLER',
comments => 'test using metadata argument',
enabled => TRUE
);
END;
/

Because the job is enabled at the creation time and we have not tied the job to a schedule, an event, or a window, the job immediately starts running. So it makes sense to check the contents of the log table. Despite the fact that the job itself has no arguments, the job name is passed to the program that inserted it into the log table.

Select job_name from log;

This clearly reveals the name of the job that caused the program to be executed: TEST_M_ARG.

This example might look a bit silly, but this mechanism gives us countless possibilities. The other attributes have their own different uses. For example, the window_end attribute enables a job to find out how much time it has to complete its task before the window that started the job will close. This can help the code to decide whether or not to make an extra iteration to complete another batch of transactions.

Now that we have seen the mysterious metadata arguments, the normal application arguments, also known as regular arguments, are just a piece of cake. For this example, we use the same log table as for the metadata example. We create a separate procedure that also has one argument as follows:

--/
CREATE OR REPLACE PROCEDURE justaproc (v_arg varchar2) as
begin
insert into log (job_name, dat_sys) values (v_arg, sysdate);
end justaproc;
/

The procedure used by a program is defined as follows:

--/
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'P_ARG01',
program_action => 'JUSTAPROC',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
comments => 'pass an argument',
enabled => FALSE
);
END;
/

Now let's define the argument to complete the program description:

--/
BEGIN
DBMS_SCHEDULER.DEFINE_program_ARGUMENT
(
program_name => 'P_ARG01',
argument_position => 1,
argument_name => 'v_arg',
argument_type => 'varchar2'
);
END;
/

The argument type is mandatory, but it is not the type checked by the Scheduler. So, if you are going to use it, you need to test the arguments definition before using it. Now that the program is complete, we can enable it.

--/
BEGIN
dbms_scheduler.enable('P_ARG01');
END;
/

We are reaching the goal of creating a job that passes an argument to the code. Everything is in place, so create the job now:

--/
BEGIN
sys.dbms_scheduler.create_job
(
job_name => 'TEST_ARG',
program_name => 'P_ARG01',
comments => 'test using a regular argument',
enabled => FALSE
);
END;
/

You might have noticed that the job is created in the disabled state. This is the same as for the program arguments—all of the job arguments have to be defined and given a value before the job can be enabled. Failing to do so will result in ORA-27457: argument 1 of job "MARVIN.TEST_ARG" has no value. We don't want such errors, so we define the arguments before trying to enable—and run—the job:

--/
BEGIN
dbms_scheduler.set_job_argument_value
(
job_name => 'TEST_ARG',
argument_name => 'V_ARG',
argument_value => 'manual'
);
END;
/

And finally we are ready to enable the job. The job will start immediately, so we can check the log table right away. First enable the job like this:

--/
BEGIN
dbms_scheduler.enable('TEST_ARG');
END;
/

If everything goes as expected, you will find another entry in the log table with "manual" in the job_name column. If you are experimenting with job arguments, you might notice that you don't need to disable a job to give its arguments a new value. As soon as you do, the job automatically becomes invalid. When you are ready, the job will not automatically get enabled again and you need to do so manually.