jobs not running

Posted by Roshan on Sep 03, 2018; 12:23pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/jobs-not-running-tp6642.html

Oracle Database 12.2.0
RHEL 7.4

Hi Erman,

I am trying to schedule a job to run a sh file. The content of sh file is shown below

. /home/oracle/.bash_profile

ORACLE_SID=DWARE1;
export ORACLE_SID
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
 
sqlplus BIREPORT/BIREPORT > /tmp/create_stat_cbs.log <<EOF

@/exec/products/oracle/busmkt/create_stat_cbs.sql

/

EXIT

EOF


Whenever I execute the file manually, it executes. But using Oracle scheduler, it is failing

[oracle@RH-DATWHSE-DEV ~]$ cat job1.sql
SET SERVEROUTPUT ON
DECLARE
  l_job_name VARCHAR2(30);

BEGIN
  l_job_name := 'CREATE_STAT_CBS';
  DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name);


  DBMS_SCHEDULER.create_job(
    job_name        => l_job_name,
    job_type        => 'EXTERNAL_SCRIPT',
    job_action      => '/exec/products/oracle/busmkt/create_stat_cbs.sh',
    start_date           => '03-SEP-18 04.02.00 PM Indian/Mauritius',
    repeat_interval => 'FREQ=DAILY;BYHOUR=16;BYMINUTE=03;BYSECOND=0' ,
    enabled         => TRUE,
    comments => 'DAILY AT 20:00'
  );
END;
/

select OWNER,JOB_NAME,STATUS from DBA_SCHEDULER_JOB_LOG where JOB_NAME like 'CREATE%';

OWNER
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------
SYS
CREATE_STAT_CBS
FAILED

Why is it failing?

Regards,

Roshan