Adding a Notification Method Based on a PL/SQL Procedure

Step 1: Define the PL/SQL procedure.

The procedure must have one of the following signatures depending on the type of notification that will be received.

For alerts and policy violations:
PROCEDURE p(severity IN MGMT_NOTIFY_SEVERITY)

For job execution status changes:
PROCEDURE p(job_status_change IN MGMT_NOTIFY_JOB)

For corrective action status changes:
PROCEDURE p(ca_status_change IN MGMT_NOTIFY_CORRECTIVE_ACTION)

Note:
The notification method based on a PL/SQL procedure must be configured by an administrator with Super Administrator privileges before a user can select it while creating/editing a notification rule.

Step 2: Create the PL/SQL procedure on the Management Repository.

Create the PL/SQL procedure on the repository database using one of the following procedure specification:

PROCEDURE p(severity IN MGMT_NOTIFY_SEVERITY)
PROCEDURE p(job_status_change IN MGMT_NOTIFY_JOB)
PROCEDURE p(ca_status_change IN MGMT_NOTIFY_CORRECTIVE_ACTION)

The PL/SQL procedure must be created on the repository database using the database account of the repository owner (such as SYSMAN).

Step 3: Register your PL/SQL procedure as a new notification method.

Log in as a Super Administrator, click Setup and then Notification Methods from the vertical navigation bar. From this page, you can define a new notification based on 'PL/SQL Procedure'.

Make sure to use a fully qualified name that includes the schema owner, package name and procedure name. The procedure will be executed by the repository owner and so the repository owner must have execute permission on the procedure.

Create a notification method based on your PL/SQL procedure. The following information is required when defining the method:

- Name
- Description
- PL/SQL Procedure

You must enter a fully qualified

Step 4: Assign the notification method to a rule.

You can edit an existing rule (or create a new notification rule), then go to the Methods page. In the list of Advanced Notification Methods, select your notification method and click 'Assign Method to Rule'. To assign multiple rules to a method or methods to a single rule, see "Assigning Rules to Methods" or "Assigning Methods to Rules".

There can be more than one PL/SQL-based method configured for your Enterprise Manager environment.

Information about the severity types that relate to a target's availability, and how metric severity and policy violation information is passed to the PLSQL procedure is covered in the next section.

Passing Alert and Policy Violation Information to a PL/SQL Procedure
Passing metric severity attributes (severity level, type, notification rule, rule owner, or rule owner, and so on) or policy violation information to PL/SQL procedures allows you to customize automated responses to alerts or policy violations.

The notification system passes information about metric severities or policy violations to a PL/SQL procedure using the MGMT_NOTIFY_SEVERITY object. An instance of this object is created for every alert or policy violation. When an alert or policy violation occurs, the notification system calls the PL/SQL procedure associated with the notification rule and passes the populated object to the procedure. The procedure is then able to access the fields of the MGMT_NOTIFY_SEVERITY object that has been passed to it.

Example: PL/SQL Procedure Using a Severity Code

procedure send_sms_notify (severity IN MGMT_NOTIFY_SEVERITY)
as
g_data varchar2(4000);
begin
IF severity.severity_code = MGMT_GLOBAL.G_SEVERITY_CRITICAL
THEN
BEGIN
g_data := severity.target_name ' - ' severity.metric_name ' - ' severity.message;
send_sms ('081xxxxxxx', g_data, 'monitor'); -- send_sms is pl/sql, that send SMS (no in Oracle).

EXCEPTION
WHEN OTHERS
THEN
-- If there are any problems then get the notification retried RAISE_APPLICATION_ERROR(-20000, 'Please retry');
END;
END IF;

end send_sms_notify;