I am using Oracle Advanced queues to enqueue messages and then dequeue them by using PLSQL callback notification in which I have registered a procedure. Whenever a message is queued, DBMS scheduler starts and it takes about 2 minutes to dequeue the message and finish the job. If in those two minutes I recompile the child procedure that register procedure uses then DBMS scheduler will not load the new state of the procedure. The issue is if DBMS scheduler is running and a child package is recompiled and mean while a new message is enqueue then the dequeue process will fail with “ORA-06508: PL/SQL: COULD NOT FIND PROGRAM UNIT BEING CALLED” exception and will move the message to exception queue. Since scheduler had to do one more task now so the instance will take longer to finish and if we keep submitting messages than scheduler will keep running and all messages will fail with same exception. Once the current instance of scheduler finishes its job it will then reload the new state of the package and the future message will be processed correctly.
One way to fix this is if I set the job_queue_processes to 0 before recompiling the package and then set it back to its initial value but I am not sure if this is the best approach The ideal solution will be to force the DBMS scheduler to load the new instance of the child procedure as soon as they are modified but I am unable to find a way to do it.
