Obsessed with Oracle PL/SQL

A few weeks ago, Mike Hichwa asked me to come up with a package that would implement a simple alerting utility: specify a triggering event (based on a query or a PL/SQL expression) and then take the specified actions when it is triggered. Seeing as he is my boss, I said "OK" and got to work (my favorite kind of work: writing some PL/SQL code). We did our usual bit to expand scope, then did our usual bit to agree that this is enough for a first pass. And then Mike said: "Go ahead and write a blog post about it, share it with the community." So here goes. The basic idea is we start up a job using DBMS_SCHEDULER that runs every N minutes. When it runs, it checks to see if any alerts need to be triggered. If so, it then takes one or more actions associated with that alert. Let's start with our tables. Utility configuration How often should the job wake up? Should it disable checking for alerts? What is the name of the "callout" for sending ema...

An Oracle Database developer* contacted me recently with this problem: Do you know a way to get the DBMS_SCHEDULER Job Name from within the code that is being executed by the job? I could have told him to visit the OTN SQL and PL/SQL Forum and post his question there, but I thought that instead I would ask the players at the PL/SQL Challenge if they had any ideas. So I posted this message in the Recent News section: Solution from Niels Hecker -- the user needs the following privileges directly assigned: -- EXECUTE on package DBMS_Lock -- SELECT ANY DICTIONARY -- CREATE JOB ------------------------------------------------------------------ -- create logging-table with associated log-procedure CREATE TABLE tbl_LogMsg ( ID INTEGER, Stamp TIMESTAMP(3), Msg VARCHAR2(4000) ); CREATE SEQUENCE seq_LogMsg#ID START WITH 0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 4294967295 ORDER NOCACHE NOCYCLE; CREATE OR REPLACE PROCEDURE LogMsg (pM...