Event driven jobs:

1.	Create an abstract data type to define the events:

create type incoming_t as object(event_type number);

2	Create a queue table to store queues that use this object type:

execute dbms_aqadm.create_queue_table(queue_table=>'incoming_qt',-
queue_payload_type=>'incoming_t',multiple_consumers=>true);

3.	Create a queue in the table:

exec dbms_aqadm.create_queue(-
queue_name=>'incoming_q',queue_table=>'incoming_qt');

4.	Create a de-queuing agent, and add it to the queue:

exec dbms_aqadm.create_aq_agent('inc_agent');
exec dbms_aqadm.add_subscriber(-
'incoming_q',sys.aq$_agent('inc_agent',null,null));

5.	Start the queue:

exec dbms_aqadm.start_queue(queue_name=>'incoming_q');

6.	Create a job that will monitor the queue and run when a certain event is enqueued:

exec dbms_scheduler.create_job(-
job_name => 'load_data',-
job_type => 'stored_procedure',-
job_action => 'read_file',-
start_date => systimestamp,-
queue_spec => 'incoming_q,inc_agent',-
event_condition => 'tab.user_data.event_type=1',-
enabled => true);

7.	Include code in the event generating application(s) to enqueue messages:

declare 
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
msgid raw(16);
payload incoming_t;
begin
payload := incoming_t(1);
dbms_aq.enqueue(queue_name => 'incoming_q',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => payload,
msgid => msgid);
commit;
end;

8.	Prove that the job did run:

select  JOB_NAME , log_Date from user_SCHEDULER_JOB_RUN_DETAILS
where job_name like 'LOAD%';

1) Home Grown Sentinal Method (trigger,table,agent)
2) Alerts (9i) using DBMS_ALERT
3) Change Notification using DBMS_CHANGE_NOTIFICATION
4) Advanced Queueing using DBMS_AQ

How would it matter if I lost notification of events on a row or two?
How would it matter if I lost notification of events on a lot of rows?
How would it matter if I processed the notification event on the same row more than once?
How would it matter if there was a delay in processing an event notification of minutes or even days?
How would it matter if notification events were processed in an unknown order?

SQL> create table t (dt date);

Table created.

SQL> var jno number;
SQL> declare jno pls_integer; 
  2  begin
  3    dbms_job.submit(:jno, 'begin insert into t values(sysdate); commit; end;');
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> print jno
       JNO
----------
        52

SQL> select * from dba_jobs where job=52;

no rows selected

SQL> select * from t;
DT
-------------------
16/04/2012 20:50:05

1 row selected.

CREATE OR REPLACE TRIGGER orders_after_insert
    AFTER INSERT
        ON x
        FOR EACH ROW

DECLARE          
 v_mr varchar2(100) := '';       
 v_sql varchar2(1000) := ''; 
 jno pls_integer ; 

BEGIN
       SELECT :new.mrn INTO v_mrn    FROM DUAL;

   V_SQL := 'BEGIN DBMS_JOB.SUBMIT ( ' || jno || ' , '' begin x_prc (' || v_mr || '); end;'' ) ; END;';
     dbms_output.put_line (V_SQL);  

  EXECUTE IMMEDIATE v_sql ;    
     
    END;

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT ON x FOR EACH ROW
DECLARE          
  jno pls_integer; 
BEGIN
  DBMS_JOB.SUBMIT (jno, 'x_prc (' || :new.mrn || ');');
END;
/