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

> 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...

Cannot reproduce on Solaris 8, Oracle 10.2.0.2.0 64-bit. Try to see/log everything the shell executes with xtrace and verbose (set -xv).

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

What is "0653-341"?

Regards
Dimitre Received on Wed Nov 01 2006 - 04:46:50 CST