DBMS_JOB: different execution behaviour?

It appears that a program which is running under DBMS_JOB may execute differently to if it were running directly (e.g.) in sql*plus.

A packaged procedure with no arguments is run every minute under dbms_job, and the job was submitted by user U. The procedure which runs actually processes 'requests' from a table records a status for each 'request' it processes to a table T. If an exception occurs during execution, the status of the 'request' is set to 'R' for retry. Strangely, when this procedure is executed by dbms_job, it is continually failing on a given 'request' and marking it as 'R'.

When I run exactly the same procedure under sql*plus as user U, it executes perfectly and the 'request' is marked as completed successfully.

As the procedure has no arguments, I can only assume that there are maybe some privilege-related issues somewhere, although obviously I would expect the procedure to execute as user U and with U's privileges in both cases....

Any thoughts, chaps?

Jeremy Ovenden
HazelWeb Systems
UK Received on Wed Sep 15 1999 - 16:06:19 CDT