Executing External Programs From Within Oracle

Finally, Oracle 10g has given us the ability to execute
external programs from within Oracle. Let’s take a look at what we need to do
to expose this new feature.

DBMS_JOB

It must first be stated, or you might get
confused as I did, that the DBMS_JOB package that was widely used in earlier
versions of Oracle is now being left behind in Oracle 10g for the greater
capabilities of the DBMS_SCHEDULER package. This new package allows us to
manage jobs with greater versatility.

DBMS_SCHEDULER

This new job scheduler allows the database to handle a much
fuller set of scheduling and monitoring capabilities. The vast amount of
capabilities of the scheduler are beyond the scope of this article but I would encourage
you to explore the manuals and determine which of the features will fit in your
environment. I am confident you will switch from the old DBMS_JOBS to the new
DBMS_SCHEDULER. If there was one feature that will get you to switch
DBMS_SCHEDULER, it is its extended ability to handle a multitude of recurring
time intervals. In addition, with this new scheduler we have the ability to
control the execution of internal database applications as well as external
applications. The new DBMS_SCHEDULER has three distinct parts.

1.  Schedule,
which is the definition of the dates, times, and recurring events that should
happen.

Note: We will
not be creating any schedules in this article.

2.  Program,
which defines the task or collection of tasks a schedule or job, will run.

3.  Job,
which is the definition of when a program will run.

Create a Program

As stated above, the program is a definition of what
external program or application we will run. Listing 1 and Table 1 give you
examples of how to define an external program to execute and what the
parameters are used for. Listing 2 also gives you the external shell contents
that are being executed from this scheduled program. Within this shell, you can
execute anything that you would normally execute from within a shell script.

Listing 1.
Create a program to execute an external application


BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => ‘VMSTAT_PGM’,
program_type => ‘EXECUTABLE’,
program_action => ‘/home/oracle/vmstat.sh’,
enabled => TRUE,
comments => ‘generate vmstat output’
);
END;
/

Table 1.

CREATE_PROGRAM
Parameters 

Parameter

Description

program_name

This parameter allows you to assign a unique name for the
program.

Program_type

This parameter indicates the type of program that will be
run. This type can take three values : plsql_block, stored_procedure, or for
our example executable.

Program_action

This parameter defines what will be run.

enabled

This parameter is a flag that determines if the program
should be enabled when created.

comments

Provide any comments to the schedule here.

Listing 2.
The External Program


[[email protected] oracle]$ cat vmstat.sh
/usr/bin/vmstat >> /tmp/vmstat.LST

Creat a Job

Now that we have a program to execute defined,
we must tell the database when to execute it. This is done by the creation of a
job. Listing 3 and Table 3 give you an example of how to create the job and the
parameters associated with the create statement.

Listing 3.

Creating a job to execute


BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘VMSTAT_JOB’,
program_name => ‘VMSTAT_PGM’,
repeat_interval => ‘FREQ=SECONDLY;INTERVAL=5’,
enabled => TRUE,
comments => ‘Every 5 seconds’);
END;
/

Table 3.

CREATE_JOB
Parameters 

Parameter

Description

job_name

This parameter allows you to assign a unique name for the
program you are creating.

program_name

This parameter allows you to associate a program name for
the job you are creating.

repeat_interval

This parameter defines the recurring time interval for
this job.

enabled

This parameter is a flag that determines if the job should
be enabled when created.

comments

Add any comments to what you are doing here.

James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles