Strange oracle job behavior

Question

I face a problem with an oracle job

This job runs every 10 min and it calls a procedure from a package. Inside the procedure, there is a select and then a loop. The select could return from 10 to 1000 rows

For one week everything was running fine (, but suddenly it is like the job is not calling the procedure. It runs successfully every 10 minutes but the procedure is not affecting the rows.

I run the procedure on its own and it works properly.

DBMS Scheduler Run details not showing anything. Everything was successfull. The only difference it that before the problem the run duration was 5 to 30 seconds, and after the problem the duration is just one second.

Do you know what else to look?

Answer 1

Log what's going on within the procedure. How? Create an autonomous transaction procedure which inserts log info into a separate table and commits; as it is an autonomous transaction procedure, that commit won't affect the rest of the transaction (i.e. the main procedure itself).

Log every step of the procedure and then review the result. There's probably something going on, but - it is difficult to guess what. One option might be that you used the

exception
  when others then null;

exception handler which successfully hides the problem.