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.