Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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 -
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