{"id":326,"date":"2009-07-28T20:24:17","date_gmt":"2009-07-28T18:24:17","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=326"},"modified":"2009-07-28T20:24:17","modified_gmt":"2009-07-28T18:24:17","slug":"database-event-triggers","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=326","title":{"rendered":"Database Event Triggers"},"content":{"rendered":"

Currently I am working on an application that runs one or more listener threads using scheduled tasks (DBMS_SCHEDULER<\/a>). 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\u2019t compile. In a later stage I found out that the scheduled tasks was the problem.<\/p>\n

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\u2019t exist. Now what?!?<\/p>\n

<\/p>\n

Fortunately there is a before and an after create trigger available. But I am replacing code? Create or replace package\u2026 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.<\/p>\n

\"compileThe easy way, when developing, was to change the columns that control the number of listeners to 0 but this wouldn\u2019t shut down the processes until a commit was issued. And you cannot commit in a trigger. Fortunately Oracle<\/a> 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.<\/p>\n

 <\/p>\n

\"better<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

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\u2019t need to trick the Oracle database with the autonomous transaction and after the compile the processes will be running again, so there\u2019s no need to manually start them up anymore.<\/p>\n<\/p>\n<\/p>\n

 <\/p>\n

 <\/p>\n

The database event triggers were introduced in Oracle 8i (8.1.7 according to http:\/\/tahiti.oracle.com<\/a>), but I haven\u2019t found a good use for them, until now.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5],"tags":[],"class_list":["post-326","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/326","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=326"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/326\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=326"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=326"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=326"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}