Execute Shell Script from Oracle Procedure

The below steps explains how to execute shell script from procedure using DBMS_SCHEDULER.

Root access is required for the steps below.

1. Ensure the configuration file $ORACLE_HOME/rdbms/admin/externaljob.ora is owned by root:

cd $ORACLE_HOME/rdbms/admin/
chown root externaljob.ora

$ ls -l externaljob.ora
-rw-r----- 1 root centos 1534 Dec 22 2005 externaljob.ora

2. Ensure the file permissions are correctly set for $ORACLE_HOME/rdbms/admin/externaljob.ora.
Remove write privileges from group and other.

chmod 640 externaljob.ora
ls -la externaljob.ora
-rw-r----- 1 root oinstall 1537 Sep 13 09:24 externaljob.ora

3. Edit $ORACLE_HOME/rdbms/admin/externaljob.ora and set run_user to the OS account that owns the Oracle installation and the database and run_group to the OS group that owns the Oracle_Home.

run_user = oracle
run_group = dba

4. Ensure the setuid bit is set on the $ORACLE_HOME/bin/extjob executable.

cd $ORACLE_HOME/bin
chmod 4750 extjob

$ ls -l extjob
-rwsr-x--- 1 root centos 64911 Jun 17 01:43 extjob

5. Run below procedure

Connect / as sysdba

create or replace procedure PrcCreateOsDir(p_path IN VARCHAR2,p_filename IN VARCHAR2)
is
v_object_job number:=0;
v_object_prg number:=0;
begin
select count(1) into v_object_job from user_objects where object_name='CREATEDIRJOB';
select count(1) into v_object_prg from user_objects where object_name='CREATEDIRPROG';
IF v_object_prg = 0 THEN
DBMS_SCHEDULER.create_program(program_name => 'createdirprog',
program_type => 'EXECUTABLE',
program_action => '/oracle/shell/createdir.sh',
number_of_arguments => 2,
comments => 'Program to create directory at host level.');
END IF;
IF v_object_job = 0 THEN
DBMS_SCHEDULER.CREATE_JOB(job_name => 'createdirjob',
program_name => 'createdirprog');
END IF;
DBMS_SCHEDULER.define_program_argument (program_name => 'createdirprog',
argument_name => 'path',
argument_position => 1,
argument_type => 'VARCHAR2');
DBMS_SCHEDULER.define_program_argument (program_name => 'createdirprog',
argument_name => 'filename',
argument_position => 2,
argument_type => 'VARCHAR2');
DBMS_SCHEDULER.ENABLE(name => 'createdirprog');
Dbms_Scheduler.Set_Job_Argument_Value(Job_Name => 'createdirjob',
argument_Name => 'path',
argument_Value => p_path);
Dbms_Scheduler.Set_Job_Argument_Value(Job_Name => 'createdirjob',
argument_Name => 'filename',
argument_Value => p_filename );
DBMS_SCHEDULER.run_job(job_name => 'createdirjob');
end;
/

6. Shell script to create directory

$cat createdir.sh
#!/bin/bash
mkdir $1/$2

7. Execute procedure to create directory

SQL> exec PrcCreateOsDir('/oracle/data','mydir');

PL/SQL procedure successfully completed.