Currently I am working on an application that runs one or more listener threads using scheduled tasks (DBMS_SCHEDULER). When I want to compile a piece of code that is effectively locked by one of these processes my IDE locks up, until I shut down the tasks. The first couple of times I was wondering what was happening and why my code wouldn’t compile. In a later stage I found out that the scheduled tasks was the problem.
It was rather easy to fix this issue. Just stop all the running listeners and my IDE frees up again. This is something that should be done more easily. If only I had only had access to an ONCOMPILE trigger. Unfortunately that doesn’t exist. Now what?!?
Fortunately there is a before and an after create trigger available. But I am replacing code? Create or replace package… The create triggers fire even when replacing code. In the before create trigger I can shut down the tasks, compile the code and in the after create trigger I can start them up again.
The easy way, when developing, was to change the columns that control the number of listeners to 0 but this wouldn’t shut down the processes until a commit was issued. And you cannot commit in a trigger. Fortunately Oracle provides a work around for this as well, namely the autonomous transaction. Since a trigger cannot contain an autonomous transaction itself, but it can call a program that is autonomous I created a package that did the transaction for me.
A better approach is to use the API to shutdown the processes before compile and bring them back up when done compiling. This way we don’t need to trick the Oracle database with the autonomous transaction and after the compile the processes will be running again, so there’s no need to manually start them up anymore.
The database event triggers were introduced in Oracle 8i (8.1.7 according to http://tahiti.oracle.com), but I haven’t found a good use for them, until now.