Passing Job Execution Status to a PL/SQL Procedure

The notification system passes job status change information to a PL/SQL procedure via the event_msg.event_payload object where event_type is equal to job_status_change. An instance of this object is created for every status change. When a job changes status, the notification system calls the PL/SQL p(event_msg IN gc$notif_event_msg) procedure associated with the incident rule and passes the populated object to the procedure. The procedure is then able to access the fields of the event_msg.event_payload object that has been passed to it.

Table 6-15 lists all corrective action status change attributes that can be passed:

Table 6-15 Job Status Attributes

Attribute Datatype Additional Information

event_msg.event_payload.source.source_name

VARCHAR2(128)

The job name.

event_msg.event_payload.source.source_owner

VARCHAR2(256)

The owner of the job.

event_msg.event_payload.source.source_sub_type

VARCHAR2(32)

The type of the job.

event_msg.event_payload. event_attrs(i).value where event_attrs(i).name=' execution_status'

NUMBER

The new status of the job.

event_msg.event_payload. event_attrs(i).value where event_attrs(i).name='state_change_guid'

RAW(16)

The GUID of the state change record.

event_msg.event_payload.source.source_guid

RAW(16)

The unique id of the job.

event_msg target.event_payload. event_attrs(i).value where event_attrs(i).name=' execution_id'

RAW(16)

The unique id of the execution.

event_msg.event_payload.target

gc$notif_target

Target Information object..

event_msg.msg_info.rule_owner

VARCHAR2(64)

The name of the notification rule that cause the notification to be sent.

event_msg.msg_info.rule_name

VARCHAR2(132)

The owner of the notification rule that cause the notification to be sent.

event_msg.event_payload. reported_date

DATE

The time and date when the status change happened.

When a job status change occurs for the job, the notification system creates an instance of the event_msg.event_payload. event_attrs(i).value where event_attrs(i).name=' execution_status' object and populates it with values from the status change. The following status codes have been defined as constants in the MGMT_JOBS package and can be used to determine the type of status in the job_status field of the event_msg.event_payload. event_attrs(i).value where event_attrs(i).name=' execution_status' object.

Table 6-16 Job Status Codes

Name Datatype Value

SCHEDULED_STATUS

NUMBER(2)

1

EXECUTING_STATUS

NUMBER(2)

2

ABORTED_STATUS

NUMBER(2)

3

FAILED_STATUS

NUMBER(2)

4

COMPLETED_STATUS

NUMBER(2)

5

SUSPENDED_STATUS

NUMBER(2)

6

AGENTDOWN_STATUS

NUMBER(2)

7

STOPPED_STATUS

NUMBER(2)

8

SUSPENDED_LOCK_STATUS

NUMBER(2)

9

SUSPENDED_EVENT_STATUS

NUMBER(2)

10

SUSPENDED_BLACKOUT_STATUS

NUMBER(2)

11

STOP_PENDING_STATUS

NUMBER(2)

12

SUSPEND_PENDING_STATUS

NUMBER(2)

13

INACTIVE_STATUS

NUMBER(2)

14

QUEUED_STATUS

NUMBER(2)

15

FAILED_RETRIED_STATUS

NUMBER(2)

16

WAITING_STATUS

NUMBER(2)

17

SKIPPED_STATUS

NUMBER(2)

18

REASSIGNED_STATUS

NUMBER(2)

20

Example 6-16 PL/SQL Procedure Using a Status Code (Job)

CREATE  TABLE job_log (jobid RAW(16), status_code NUMBER(2), occured DATE);
 
CREATE OR REPLACE PROCEDURE LOG_JOB_STATUS_CHANGE(event_msg IN GC$NOTIF_EVENT_MSG)
IS
  l_attrs gc$notif_event_attr_array;
  exec_status_code NUMBER(2) := NULL;
  occured_date DATE := NULL;
  job_guid RAW(16) := NULL;
 
BEGIN
  IF event_msg.event_payload.event_type = 'job_status_change'
  THEN
    l_attrs := event_msg.event_payload.event_attrs;
    IF l_attrs IS NOT NULL
    THEN
      FOR i IN 1..l_attrs.COUNT
      LOOP
        IF l_attrs(i).name = 'exec_status_code'
        THEN
          exec_status_code := TO_NUMBER(l_attrs(i).value);
        END IF;
      END LOOP;
    END IF;
 
    occured_date := event_msg.event_payload.reported_date;
    job_guid := event_msg.event_payload.source.source_guid;
    -- Log all jobs' status
    BEGIN
      INSERT INTO job_log (jobid, status_code, occured)
      VALUES (job_guid, exec_status_code, occured_date);
    EXCEPTION
    WHEN OTHERS
    THEN
      -- If there are any problems then get the notification retried
      RAISE_APPLICATION_ERROR(-20000, 'Please retry');
    END;
    COMMIT;
 
  ELSE
    null; -- it is not a job_status_change event, ignore
  END IF; 
END LOG_JOB_STATUS_CHANGE;
/