Re: Guru's advice needed on dbms

From: John Beresniewicz <jberesniewicz_at_comcast.net>
Date: Mon, 08 Jul 2002 13:41:07 GMT
Message-ID: <TDgW8.29241$Im2.855539@bin2.nnrp.aus1.giganews.com>

Flavio -

You can catch an exception generated within the job procedure by remembering that the job is simply a PL/SQL block and can therefore include an exception handler. Assume you have table called JOB_FAILS with columns (JOB_NUMBER, FAIL_DATE, ORACLE_ERROR) and that the job you want to run is a procedure called PROC1.

Then you can submit your job like this:

DECLARE
jobno integer;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'DECLARE job_number integer; errcode integer; ' ||

                  'BEGIN job_number:= job; PROC1; ' ||
                  'EXCEPTION when others then errcode := sqlcode; '||
                  ' insert into
JOB_FAILS(job_number,sysdate,sqlerrm(errcode) ); '||
                  'END;' ,

interval => null , -- since you said it does not get resubmitted next_date => SYSDATE -- run right away );
COMMIT;
END; Now you will "catch" the exception generated by PROC1 into the JOB_FAILS table for debugging or other analysis.

A quick and dirty way to try and detect the exception is to run the job in your own session using DBMS_JOB.RUN procedure, however it is possible for jobs to complete in a user session but fail when executed by the job queue processes.

"FC" <flavio_at_tin.it> wrote in message
news:DvfW8.54892$vm5.2026181_at_news2.tin.it...
> Hello folks,
> speaking 8.1.7.0.0. Win2000wise, does anybody know where I can find
> information about errors occurring while running a job submitted via
> dbms_job.submit ?
> I am specifically looking for any error codes that might have prevented
the
> job from completing.
> I checked in dba_jobs and user_jobs views, but they merely report if a job
> is broken, I need some more details.
> I'd like to know also when the job completed, but it seems that the job
> entry is removed from the job queue once it's finished.
> Theoretically I could send a message using dbms_alert, but I am having
> problems using this package from within scheduled jobs, anybody's aware of
> any restrictions on this kind of usage ?
>
> Thanx!
> Flavio
>
>
>
>
Received on Mon Jul 08 2002 - 08:41:07 CDT

Re: Guru's advice needed on dbms_job related question