Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Job history?

Re: Job history?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 04 Feb 2007 14:02:31 -0800
Message-ID: <1170626550.842987@bubbleator.drizzle.com>


jobs wrote:
> I've got an Oracle function that I'm using to launch stored procedures
> via dbms_job.submit. The function returns me a job number. when the
> job completes with no problem, the job disappears.
>
> I need to do some error handle and tracking on jobs (sps really) that
> fail. I'd like to somehow be able to determine and report something
> about the reason an SP (run as a job) failed.
>
> I'm testing with some code I make fail on purpose. for lack of better
> code I'm simply doing this:
>
> PROCEDURE X_FAIL
> IS
> Begin
> dbms_lock.sleep(50);
> EXECUTE IMMEDIATE 'THIS SHOULD FAIL;';
>
> End;
>
>
> My front end (asp.net/vb.net/sqlserver) calls the above SP, though an
> oracle function that uses
> dbms_job.submit. I know that function works as I've used it to kick
> off good stored procedures.
>
> When I use the function to launch the above sp, I get back a job
> number and I see an entry in
>
> select * from ALL_JOBS
>
> however broken so far Broken is reporting 'N' and Failures is
> reporting 3.
>
> My questions -
>
> 1. Does this mean it's tired to run the sp 3 times? If so, I'll need
> to address that.
> 2. How do I confirm the sp did in fact fail, I was hoping broken would
> show up as Y
> 3. where do I go for detailed information about the failure, any way
> to trap and send that back or store it somewhere I can then tie to the
> oracle job numer?
> 4. Finally, when job do seem to run good, the entry from all_jobs
> disappears. Again, is there any way to look up history on the job to
> confirm it did in fact run to completion? Maybe another table?
>
> My big challenge is that I I'm calling dynamically selected SP from a
> function and calling that function from outside of Oracle. I also
> won't have access to the filesystem where oracle can write to --
> what's the best way for me to get back information about steps
> performed in SP.
>
> Thank you VERY much for any help or information - it's greatly
> appreciated!

http://www.psoug.org/reference/exception_handling.html

-- 
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 Sun Feb 04 2007 - 16:02:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US