I'm using Oracle 7.3.4. I have a BEFORE UPDATE trigger that runs a stored
procedure, then submits a database job to run another stored procedure as
the process that submitted the UPDATE continues. The job is successfully
submitted, but breaks. No notification or error was raised indicating that
the stored procedure failed. When I manually execute the stored procedure
executed by the job, it finishes without errors. When I manually submit an
appropriate UPDATE statement, the trigger fires, the job is submitted, and
the stored procedure finishes without errors. I've used this trigger/db
job/stored procedure scheme successfully with the UPDATE being issued by yet
another stored procedure. Typically, when the procedure executed by the job
fails, the error is raised up to the procedure that issued the UPDATE, and
the top-level procedure then reports an error. I'm trying to figure out why
the top-level procedure may or MAY NOT receive the error procedure called by
the job. When I look at the ALL_JOBS view, it appears that the context in
which the job is running is the owner of the triggered table. What's
confusing is that this is the same schema as the top-level stored procedure.
I realize that the job is probably running under a background process, but
isn't there some sort of connection between the calling connection and that
background process? How is it that broken jobs sometimes appear to raise
errors, other times not?
Thanks,
Neal
Received on Thu Aug 08 2002 - 16:22:23 CDT
