Re: Job history?

> http://www.psoug.org/reference/exception_handling.html
> --
> Daniel A. Morgan

Thank you that was very informational. I guess I'm trying to build a common front end scheduler for a bunch of existing processes (oracle and sql server) and really don't want to have to modify or standardize existing proceses if possible. Looking at one of the Oracle SPs I plan and the exception hanlding they are doing:

         when OUTPUT_WITH_ERROR then
              glglosa_error := SQLERRM(sql_error);
              utl_file.fclose_all;
              dbms_output.put_line('Outputf +++');
              dbms_output.put_line('SQL_ERROR = '|| glglosa_error);
         when OTHERS then
          ...

I suspect these errors show up on the stdout or stderror of the shell script that called the sql that calls the SP.

Should I presume that I'm going to going to need to add or replace some of that code to write to a table somewhere ? Worse case i can do that, looking for the errors in my custom table as part of my check step - but I have to say if thats the way Oracle expects you to do error handling of jobs, it's got nothing on sQL server 2005 where it's all nice and pretty... something tells me somewhere Oracle must have this and way to connect the dots to the job number.. but I'm a Oracle noob and have been unable to find the easy answer.

So my two big questions remain, Oracle is apparently aware that the job had errors as it remains in the the all_jobs table.

1. Oracle knew enough to know the job had errors, but does not *automatically* store the error by Job number anywhere??
2. Oracle apparently keeps trying to re-run the failing job as the failure count keeps going up - why? and how can I stop that?

Thanks again. Received on Sun Feb 04 2007 - 16:34:32 CST