DBMS_SCHEDULER - Secrets of the Oracle Database - page 186

Command Line Processing
Another undocumented aspect concerns how the scheduler runs the PROGRAM_ACTION specified
with one of the procedures DBMS_SCHEDULER.CREATE_PROGRAM or DBMS_SCHEDULER.CREATE_JOB .
The Oracle Database PL/SQL Packages and Types Reference 10g Release 2 informs us that “the
PROGRAM_ACTION for a program of type EXECUTABLE is the name of the external executable, including
the full path name and any command-line arguments” (page 93-35, [OL10 2005]). The first part
of this statement is correct, since the attempt to run an external program without specifying
the full path name fails with “ORA-27369: job of type EXECUTABLE failed with exit code: No
such file or directory”. After a job failure, scheduler error messages may be retrieved from the
column ADDITIONAL_INFO of the data dictionary view DBA_SCHEDULER_JOB_RUN_DETAILS .
The second part, however, solely applies to Windows. On UNIX, arguments to external
programs must be defined with the procedure DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT
instead of adding them to the PROGRAM_ACTION . Otherwise jobs based on the program fail with
“ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory”. External
jobs, which are not based on programs, cannot receive arguments.
On UNIX, the program $ORACLE_HOME/bin/extjobo is responsible for running external jobs
owned by SYS. Using a system call trace utility such as truss or strace reveals that extjobo uses
the UNIX system call access to verify that the program action is executable. A program action
that includes arguments fails this test with the UNIX error ENOENT (No such file or directory).
System call tracing also reveals that the executable is run directly with the system call execve .
This implies that characters such as ? or * within program arguments, which have special
meaning to shells, must not be escaped.
Another implication is that the pseudo comment #!/bin/sh (or #!/bin/ksh , #!/bin/bash ,
etc.), which specifies an interpreter for running text files, must be present in the first line of
shell scripts. If the specification of a command interpreter is missing, the error “ORA-27369:
job of type EXECUTABLE failed with exit code: 255” is reported and DBA_SCHEDULER_JOB_RUN_
DETAILS.ADDITIONAL_INFO contains STANDARD_ERROR="execve: Exec format error".
Likewise, perl must be specified as the command interpreter in order to run Perl scripts as
external jobs. Thus, the first line of a Perl script might specify the absolute path to the perl
interpreter as shown here:
#!/opt/oracle/product/db10.2/perl/bin/perl
The more portable approach is to use /usr/bin/env , which has the same absolute path on
all UNIX systems, to run perl as follows:
#!/usr/bin/env perl
The disadvantage of this latter approach in the context of the database scheduler is that
the environment variable PATH is removed by extjobo . Thus, some mechanism that sets PATH
before the Perl script is run must be in place. Once more /usr/bin/env may be used for this
purpose, by defining a program that calls env and sets PATH in a program argument. Following
is an example. The Perl script test.pl , which shall be executed by the scheduler, contains the
code shown here:
#!/usr/bin/env perl
printf STDERR "This is perl script $0 executed by UNIX process $$.\n";
exit 0;