Call executable file from a DBMS_SCHEDULER job

This section shows way to call an executable file from an Oracle DBMS_SCHEDULER job.

Business Requirement

Business user needs to run a shell script executable on demand. User should be able to run it from any Oracle IDE tool.

Solutions

Below is one of the possible solution to meet the given business requirement.

Solution TOP

To perform given steps, following permissions are required:
i) sudo to oracle user at Unix OS level
ii) SYSDBA privilege on DB

Create a shell script as Oracle user in unix directory:

cd /home/oracle

cat rp.sh

#!/bin/ksh
export ORACLE_SID=ORCL1
export  ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH

exec 1> my_log_file.log 2>&1

sqlplus -s / as sysdba --EOF
        set serveroutput on;
        set linesize 500
        SET TRIMOUT ON
        set trimspool on
        set feedback off

        SELECT EXTRACT(YEAR FROM SYSDATE) as "Year" FROM DUAL ;

        exit
EOF

#set its permission
chmod 775 rp.sh
		

Now set the SID and login as SYS user into database:

. oraenv
ORACLE_SID = [ORCL1] ? ORCL1

sqlplus / as sysdba

--Create a scheduler job as SYS user
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'SAMPLE_JOB_EXEC',
    job_type        => 'executable',
    job_action      => '/home/oracle/rp.sh',
    enabled         => TRUE ,
    auto_drop       => FALSE,
    comments        => 'Sample job to call OS executable from Oracle.');
END;
/

--Grant required permissions to the user to run this job:

GRANT EXECUTE on DBMS_SCHEDULER to buser01;
GRANT ALTER ON SAMPLE_JOB_EXEC to buser01;	

--Now business user can run the job via
exec DBMS_SCHEDULER.RUN_JOB (   'SYS.SAMPLE_JOB_EXEC'     );
		

In OS directory, you may check the generated log file:

cat my_log_file.log

      Year
----------
      2021

Few caveats:

i) If scheduler job is not created as SYS user, following error may occur:

ORA-27369: job of type EXECUTABLE failed with exit code: 274662 STANDARD_ERROR="Oracle Scheduler error: Config file is not owned by root

ii) If proper grants are not in place, following error may occur:

ORA-27475: unknown job the specified object did not exist, privileges were not granted

Thanks...

TOP