1 Reply Latest reply: Jun 19, 2013 4:32 AM by spajdy RSS

    Can't Seem to Get my Event Based Job To Execute

    Joe Upshaw

      What makes this slightly different than other posts on this topic is that we want to use our own queue/payload object rather than the built-in SCHEDULER$_EVENT_QUEUE. There are many reasons for this. What follows in under 11gR2 (11.2.0.3)

       

      So, the following code blocks are just for a proof of concept piece intended to create a simple job and to have it fired based on the arrival of a message in a queue. (Something we have a requirement to do) The issue that I just can't seem to resolve is that, for some reason, the scheduler object never triggers the job. I can't find any trace or alert log entries to help me diagnose. What I do know is that the queue itself is fine (dequeues and enqueues work normally). The object payload type is fine (All of these tested from the EVENT_JOB_MGR package provided below).

       

      Here's the PL/SQL Type that is the payload object of the queue and is also referenced in the package:

       

      CREATE OR REPLACE TYPE RUN_MSG AS OBJECT

      (

        STATUS VARCHAR2(255)

      )

      /

       

      Here are the queue and queue table:

       

      BEGIN

        DBMS_AQADM.CREATE_QUEUE_TABLE

        (

          QUEUE_TABLE           =>        'JUPSHAW.EVENT_JOB_QT'

         ,QUEUE_PAYLOAD_TYPE    =>        'JUPSHAW.RUN_MSG'

         ,COMPATIBLE            =>        '10.0.0'

         ,STORAGE_CLAUSE        =>        '

                                           TABLESPACE RISKDM_DATA'

         ,SORT_LIST             =>        'ENQ_TIME'

         ,MULTIPLE_CONSUMERS    =>         TRUE

         ,MESSAGE_GROUPING      =>         0

         ,SECURE                =>         FALSE

         );

      End;

      /

       

      BEGIN

        DBMS_AQADM.CREATE_QUEUE

        (

          QUEUE_NAME          =>   'JUPSHAW.EVENT_JOB_Q'

         ,QUEUE_TABLE         =>   'JUPSHAW.EVENT_JOB_QT'

         ,QUEUE_TYPE          =>   SYS.DBMS_AQADM.NORMAL_QUEUE

         ,MAX_RETRIES         =>   0

         ,RETRY_DELAY         =>   0

         ,RETENTION_TIME      =>   -1

         );

      END;

      /

       

      DECLARE

        aSubscriber sys.aq$_agent;

      BEGIN

        aSubscriber := sys.aq$_agent('SCHEDULER$_EVENT_AGENT',

                                    '',

                                    0);

        dbms_aqadm.add_subscriber

           ( queue_name     => 'EVENT_JOB_Q'

            ,subscriber     => aSubscriber);

      END;

      /

       

      BEGIN

        SYS.DBMS_AQADM.START_QUEUE

        (

          QUEUE_NAME => 'JUPSHAW.EVENT_JOB_Q'

         ,ENQUEUE => TRUE

         ,DEQUEUE => TRUE

         );

      END;

      /

       

      (I think the scheduler automatically added itself as a subscriber (see above) after the queue itself was created)

       

      So, here's the simple package:

       

      CREATE OR REPLACE PACKAGE EVENT_JOB_MGR

      AS

          PROCEDURE DEQUEUE_JOB_STATUS_MSG;

          PROCEDURE ENQUEUE_JOB_STATUS_MSG;

          PROCEDURE RECORD_DEQUEUED_STATUS( a_RunMsg  RUN_MSG );

      END EVENT_JOB_MGR;

      /

       

      CREATE OR REPLACE PACKAGE BODY EVENT_JOB_MGR

      AS

          PROCEDURE RECORD_DEQUEUED_STATUS( a_RunMsg  RUN_MSG )

          IS

        

              ls_Status       VARCHAR2(32);

        

          BEGIN

            

              ls_Status := a_RunMsg.STATUS;

            

              INSERT INTO DEQUEUE_RECORD

              ( STATUS )

              VALUES

              ( ls_Status );

            

              COMMIT;    

            

          END;       

       

          PROCEDURE DEQUEUE_JOB_STATUS_MSG

          IS

        

              l_DeQOptions    DBMS_AQ.DEQUEUE_OPTIONS_T;

            

              l_MsgProps      DBMS_AQ.MESSAGE_PROPERTIES_T;

        

              l_DeQMsgID      RAW(16);

            

              l_RunMsg        RUN_MSG;                

        

          BEGIN

        

              l_DeQOptions.CONSUMER_NAME := 'EVENT_JOB';

        

              DBMS_AQ.DEQUEUE(    queue_name          =>  'EVENT_JOB_Q',

                                    dequeue_options     =>  l_DeQOptions,

                                    message_properties  =>  l_MsgProps,

                                    payload             =>  l_RunMsg,

                                    msgid               =>  l_DeQMsgID );

                                  

              COMMIT;                             

                                  

              RECORD_DEQUEUED_STATUS( l_RunMsg );                              

        

          END;

        

          PROCEDURE ENQUEUE_JOB_STATUS_MSG

          IS

              l_EnQOptions    DBMS_AQ.ENQUEUE_OPTIONS_T;

            

              l_MsgProps      DBMS_AQ.MESSAGE_PROPERTIES_T;

            

              l_EnQMsgID      RAW(16);

            

              l_RunMsg        RUN_MSG;               

        

          BEGIN   

            

              l_RunMsg := RUN_MSG('Success');

        

              DBMS_AQ.ENQUEUE(  QUEUE_NAME          =>  'EVENT_JOB_Q',

                                  ENQUEUE_OPTIONS     =>  l_EnQOptions,

                                  MESSAGE_PROPERTIES  =>  l_MsgProps,

                                  PAYLOAD             =>  l_RunMsg,

                                  MSGID               =>  l_EnQMsgID);

       

       

          END;

       

      END EVENT_JOB_MGR;

      /

       

      -- Finally the program, schedule and job

       

      BEGIN

       

       

          DBMS_SCHEDULER.CREATE_PROGRAM(

              PROGRAM_NAME          => 'EVENT_JOB_PROG',

              PROGRAM_ACTION        => 'EVENT_JOB_MGR.RECORD_DEQUEUED_STATUS',

              PROGRAM_TYPE          => 'STORED_PROCEDURE',

              NUMBER_OF_ARGUMENTS   => 1,

              ENABLED               => FALSE );

            

          DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT (

              program_name        => 'EVENT_JOB_PROG',

              argument_position   => 1,

              metadata_attribute  => 'EVENT_MESSAGE' );  

            

          DBMS_SCHEDULER.ENABLE( NAME => 'EVENT_JOB_PROG');        

        

      EXCEPTION

        WHEN OTHERS THEN RAISE ;       

        

      END;

      /

       

      COMMIT

      /

       

      BEGIN

        DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (

         schedule_name     =>  'EVENT_JOB_SCHED',

         start_date        =>  SYSTIMESTAMP,

         event_condition   =>  'TAB.USER_DATA.STATUS = ''SUCCESS''',

         queue_spec        =>  'EVENT_JOB_Q');

      END;

      /

       

      BEGIN

        DBMS_SCHEDULER.CREATE_JOB (

         job_name            =>  'EVENT_JOB',

         program_name        =>  'EVENT_JOB_PROG',

         schedule_name       =>  'EVENT_JOB_SCHED',

         enabled             =>  TRUE,

         comments            =>  'Start if you get a success message');

      END;

      /

       

      I call the ENQUEUE_JOB_STATUS_MSG method to stick a message on the queue. I can confirm that it can be dequeued and logged using the DEQUEUE_JOB_STATUS_MSG method in the package. However, nothing seems to happen (at all) as far as the scheduler job when a message is put on the queue. I am thinking it should dequeue the message with status set to SUCCESS and then, kick off the job.

       

      Can anyone see why the above wouldn't work? Been stuck here for a couple of days.

       

      Thanks,

       

      -Joe

        • 1. Re: Can't Seem to Get my Event Based Job To Execute
          spajdy

          Try to create job that react on event directly not using schedule.

           

          BEGIN

              dbms_scheduler.create_job(job_name => 'EVENT_JOB',

                                        program_name =>  'EVENT_JOB_PROG',

                                        event_condition => 'TAB.USER_DATA.STATUS = ''SUCCESS''',

                                        queue_spec => 'EVENT_JOB_Q',

                                        enabled => true,

                                        auto_drop => FALSE);

                                     

          END;

          /

           

          Check if your schedule EVENT_JOB_SCHED is enabled