DBMS_SCHEDULER external jobs failing but status of SUCCEEDED?!?!

From: Althalus <toby_at_technical-it.co.uk>
Date: 1 Nov 2006 00:59:44 -0800
Message-ID: <1162371584.005522.8290@k70g2000cwa.googlegroups.com>

I hope someone can help, this is very strange...

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

I have written a shell script that ultimately will load files, for now all it does is do an ls to see if the file exists.

8:00 yesterday morning I ran thje external scheduled job, the shell returned an error (the file did not exist) which was exactly the behaviour I was after -

  1* SELECT status, additional_info FROM user_scheduler_job_run_details WHERE log_id = 23484
SQL> / STATUS

---

ADDITIONAL_INFO

---

FAILED
ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory
STANDARD_ERROR="XYZ not found"

Today if I run the job again I get a status of succeeded BUT the shell failed!

  1* SELECT status, additional_info FROM user_scheduler_job_run_details WHERE log_id = 23516
SQL> / STATUS

---

ADDITIONAL_INFO

---

SUCCEEDED
STANDARD_ERROR="XYZ not found"

This is the shell script

#!/bin/ksh
cd /home/hubbatch/era/staging
ls ${1}*

Running on the server this returns -
ls: 0653-341 The file XYZ* does not exist.

The job was defined as follows
BEGIN
    dbms_scheduler.create_job(

      job_name            => 'NOV01',
      job_type            => 'EXECUTABLE',
      job_action          =>
'/home/hubbatch/era/shell/scripts/tae_load.ksh',
      number_of_arguments => 1

);

    dbms_scheduler.set_job_argument_value (

      job_name            => 'NOV01',
      argument_position   => 1,
      argument_value      => 'XYZ'

);

    dbms_scheduler.ENABLE('NOV01');
END; Since 8:00 yesterday morning as far as I am aware no database changes have been made other than after successfully getting the stand alone job to work I defined the shell as a program and created a job based on this new program.

I'm really stumped because I need to reliably know if the job failed or not...

Any help with the beast that is DBMS_SCHEDULER much appreciated! Received on Wed Nov 01 2006 - 02:59:44 CST