1.0 Inroduction

Oracle 10g introduces a new feature, scheduling external os scripts (like space reports or hot backup rman/os scripts) via dbms_scheduler. This was a welcome news for many DBAs like me. Since before this, the only way to schedule such jobs was by using operating system utilities such as cron(unix) and at (windows). There are other commercial scheduling softwares also available (like autosys from Compute Associates) for this purpose, but these softwares are expensive, so many small to medium sized oracle shops prefer to stick with cron or at. Me, I really would prefer to schedule jobs in the database instead of the operating system. Some of the reasons for my such strong opinion are as below:

a) Except for scripts which start the database, I may want some of my scripts (e.g. Gather statistics) to run only when the database is up and running. For this purpose I would have to code my script to check for availability of the database, but if my script is scheduled in the database, it would run only when the database is up.

b) The details of each job execution (like start time, exit status etc) is lost if we use cron or at, but if my script is scheduled in the database, such information are stored in the database.

c) If we migrate our database to a different platform, we will have to reschedule all our jobs to use the native scheduler. Considering for example, the syntactic differences between at and cron, if we migrate a database from unix to windows we would have to reschedule every job using at instead of cron, which would be a painstaking process. If our jobs were scheduled in the database then this would not be necessary.

So I really wanted to schedule many os scripts using oracle scheduler, but oracle did not provide this ability till 10g. So I ended up writing some custom code to accomplish this in 8i and 9i. This custom code is what I intend to describe and demonstrate in this article.

2.0 Architecture

Let me describe the design of this system first. There are basically two ways to run external code in Oracle 8/9i. Either using extercal C procedure or using java stored procedure. I chose java stored procedures over external C procedures because it does not require any setup outside the database (except for having java installed in the database).

Lets say we want to schedule /home/oracle/backup.sh -sid RAC -oh /u01/oracle/9.2.0. Following is description of each piece of my scheduling system assuming we are running the above mentioned command.

1) We schedule plsql procedure schedule_external('/home/oracle/backup.sh -sid RAC -oh /u01/oracle/9.2.0') as a job using dbms_job.submit.

2) schedule_external appends /home/oracle/extjob9i to the parameter passed to it, effectively making the command line to run: /home/oracle/extjob9i /home/oracle/backup.sh -sid RAC -oh /u01/oracle/9.2.0 and then invokes function runoscommand with this command line as the first parameter and a clob output variable as the second. Runoscommand will return the output from our command line into this clob. Runoscommand is a call specification for function runCommand in java class host.

3) The host.runCommand function creates external process and runs the command line: /home/oracle/extjob9i /home/oracle/backup.sh -sid RAC -oh /u01/oracle/9.2.0, reads the output from the command line into the clob variable passed to it by schedule_external procedure. It waits for the external process to exit, it then returns with the exit code of the external process.

4) Now for the last piece. extjob9i is the C program, which runs our script. It creates a pipe and then forks. The child closes its standard output, standard error and duplicates them over the write end of the pipe, it closes the read end of the pipe. It then execs and runs our script. Since the stdout and stderr has been duplicated to the write end of the pipe and open file descriptors remain open across exec, our script's output and error messages go into the pipe. The parent closes the write end of the pipe, reads the pipe using the read end of the pipe in a loop till the child process exits. Whenever it reads something from the pipe it prints it out on to its standard output for the java host.runCommand to read. Once the child exits the parent also exits with the same exit status.

5) Host.runCommand reads the output from extjob9i and writes it into the clob variable passed to it by schedule_external procedure. It exits with the exit status of extjob9i.

6) schedule_external stores the clob variable and the return status along with command and other information about the execution into the table execution_history.

Now I will go through each piece of system in detail. I am assuming that we are going to run:

/home/oracle/backup.sh -sid RAC -oh /u01/oracle/9.2.0.