3 Replies Latest reply: Dec 30, 2008 7:13 PM by Rnr-Oracle RSS

    Event Based scheduling

    SUPRIYO DEY
      How to create event based schedule with user-defined event not system defined events like DBMS_SCHEDULER.JOB_FAILED ?
        • 1. Re: Event Based scheduling
          653835
          Hi

          This article should help you

          http://www.oracle-base.com/articles/10g/SchedulerEnhancements_10gR2.php

          Otherwise ask more question if it is not what you are looking for.

          Regards,
          Hub
          • 2. Re: Event Based scheduling
            108476
            Hi,
            How to create event based schedule with user-defined event
            That's a mighty big topic!

            Dr. Hall has a whole book on this topic "Oracle Job Scheduling":

            http://www.rampant-books.com/book_2005_1_scheduling.htm
            • 3. Re: Event Based scheduling
              Rnr-Oracle
              Hi,

              Here's a simple complete example of using a user event to trigger a job

              Hope this helps,
              Ravi.
              -- grant necessary privileges to scott
              grant create job to scott ;
              grant execute on dbms_aq to scott;
              grant execute on dbms_aqadm to scott;

              connect scott/tiger

              -- setup a multiple consumers queue for event messages
              create or replace type msg_type as object ( col1 varchar2(20) ) ;
              /

              begin
              dbms_aqadm.create_queue_table('qt1','msg_type',multiple_consumers => TRUE);
              dbms_aqadm.create_queue ( queue_name => 'Q1', queue_table => 'QT1');
              dbms_aqadm.start_queue ( queue_name => 'Q1' ) ;
              end ;
              /

              begin
                dbms_scheduler.create_job('MYJOB',
                  job_type=>'plsql_block',
                  job_action=>'null;',
                  event_condition => 'tab.user_data.col1 is not null',
                  queue_spec =>'q1',
                  enabled=>true);
              end;
              /

              -- now enqueue message to run the job
              declare
                msg msg_type;
                my_msgid RAW(16);
                props dbms_aq.message_properties_t;
                enqopts dbms_aq.enqueue_options_t;
              begin
                msg := msg_type('test');
                dbms_aq.enqueue('q1', enqopts, props, msg, my_msgid);
              end;
              /
              commit;

              select * from all_scheduler_job_log where job_name='MYJOB' order by log_id;