Re: Job history?

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 05 Feb 2007 09:19:54 -0800
Message-ID: <1170695994.436090@bubbleator.drizzle.com>

jobs wrote:

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

My first thought looking at what you wrote is that Oracle runs on a server. There will be no one looking at the monitor when dbms_output writes SQL*Plus. Consider instead using UTL_SMTP or UTL_MAIL (depending on version) to send an email and log the exception to a table.

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

DBMS_OUTPUT only outputs to SQL*Plus.

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

I would always log exceptions in a table. Having a record allows you to perform analysis of what is going wrong, when it is going wrong, why it is going wrong, and gives you the name of the end user to blame. ;-)

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

Yes.

> 1. Oracle knew enough to know the job had errors, but does not
> *automatically* store the error by Job number anywhere??

It puts them out for the developer to use as the developer chooses. I think that would be you in this case.

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

It thinks there is a DBA, or other form of intelligence, paying attention. Stop it by either writing an error handler that fixes the problem, best practice, or stop it by disabling the job.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Feb 05 2007 - 11:19:54 CST