This section shows way to call an executable file from an Oracle DBMS_SCHEDULER job.
Business user needs to run a shell script executable on demand. User should be able to run it from any Oracle IDE tool.
Below is one of the possible solution to meet the given business requirement.
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
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 rootii) If proper grants are not in place, following error may occur:
ORA-27475: unknown job the specified object did not exist, privileges were not grantedThanks...