Re: Guru's advice needed on dbms_job related question

Though I'm not a true guru, I can add that errors occuring in batch jobs are found in the log file of the background process. You find these in the same directory as the alert log. There are as many log files as the setting of job_queue_processes.

Further the job is not resubmitted only if it fails for 8 times in a row. Don't know it this is configurable but the failure count may be reset programmatically (use dbms_jobs package).

"John Beresniewicz" <jberesniewicz_at_comcast.net> wrote in message news:<TDgW8.29241$Im2.855539_at_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.
>
> JB
>
>
>
> "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 - 13:09:09 CDT