Job history?

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! Received on Sun Feb 04 2007 - 14:25:45 CST