jobs not running

[Roshan]

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

[ErmanArslansOracleBlog]

You need to see the details of thatr failure..

You can query failed jobs by querying the *_SCHEDULER_JOB_RUN_DETAILS views.

[ErmanArslansOracleBlog]

Example query:

SELECT to_char(log_date, 'DD-MON-YY HH24:MI:SS') TIMESTAMP, job_name, status,
SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO
FROM dba_scheduler_job_run_details ORDER BY log_date;