Executing External Programs From Within Oracle

Friday Jun 11th 2004 by James Koopmann
Share:

Finally, Oracle 10g has given us the ability to execute external programs from within Oracle. James Koopmann takes a look at what needs to be done to expose this new feature.

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

[oracle@bier 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.

How to View Scheduler Information

To take a look at the jobs that have run and their success or failure, you can run the following SQL, found in Listing 4. If you are concerned with just the status of a job and whether it is scheduled to run or not you can issue the SQL in Listing 5.

Listing 4.
Show status of previously ran jobs

SQL> SELECT JOB_NAME, STATUS, ERROR#
    FROM DBA_SCHEDULER_JOB_RUN_DETAILS 
   WHERE JOB_NAME = 'VMSTAT_JOB';

JOB_NAME        STATUS           ERROR#
--------------- ---------------- ------
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0
VMSTAT_JOB      SUCCEEDED             0

Listing 5.
Status of scheduled jobs

SQL> SELECT JOB_NAME, STATE 
      FROM DBA_SCHEDULER_JOBS
      WHERE JOB_NAME = 'VMSTAT_JOB';

JOB_NAME            STATE
------------------- ---------------
VMSTAT_JOB          SCHEDULED

Looky mom, I can delete from a DBA view.

SQL> DELETE FROM dba_scheduler_job_run_details;
SQL> COMMIT;

Output From Our Example

Just to show that we actually generated some output from our external job call from within Oracle take a look at Listing 6.

Listing 6.

[oracle@bier oracle]$ cat /tmp/vmstat.LST
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  0      0  97268 187756 588084    0    0    87   102  533    98  6  1 89  4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  0      0  97268 187792 588048    0    0    87   102  533    98  6  1 89  4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  0      0  97140 187840 588000    0    0    87   102  533    98  6  1 89  4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  1      0  98612 187864 587976    0    0    87   102  533    98  6  1 89  4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  0      0  98612 187920 587920    0    0    86   102  533    98  6  1 89  4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  0      0  98612 187968 587872    0    0    86   102  533    98  6  1 89  4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  0      0  98548 188016 587824    0    0    86   102  533    98  6  1 89  4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 1  0      0  98548 188056 588044    0    0    86   102  533    98  6  1 89  4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  1      0  98612 188096 588004    0    0    86   102  533    98  6  1 89  4
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  1      0  97012 188124 587976    0    0    86   102  533    98  6  1 89  4

Droping the Program and Job

If you should ever want to drop the newly created program and job you can use the following DBMS_SCHEDULER drop procedures.

BEGIN
DBMS_SCHEDULER.DROP_PROGRAM ('vmstat_pgm');
END;
/

BEGIN
DBMS_SCHEDULER.DROP_JOB ('vmstat_job');
END;
/

The ability for us as DBAs to extend internal database scheduling to call external applications is invaluable. Personally, I no longer need to rely upon cron job entries and their limited ability to schedule my external procedures and DBA tasks. Now ALL scheduled database tasks can be scheduled within my database and be controlled by me where I have control. This is a great day.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved