Submit a ticketCall us

AnnouncementsAre You “Flying Blind?”

When it comes to your complex IT infrastructure, you want to ensure you have a good grasp of what’s going on to avoid any fire drills that result from guesswork. Read our white paper to learn how proactively monitoring your IT environment can help your organization while giving you peace of mind.

Get your free white paper.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > Writing Custom Alerts with DPA

Writing Custom Alerts with DPA

Last updated November 20, 2017


Custom Alerts enable a user to execute SQL statements or stored procedures against the monitored database or DPA repository to check for customized, site-specific conditions and to generate an alert when these conditions are met. Any parameter that can be returned to DPA using a SQL statement or stored procedure can be used as the basis for a custom alert. This article focuses specifically on creating custom alerts with DPA. The article covers custom stored procedures, alert values, custom SQL statements, and custom tags.

Key Features

  • Custom Alerts are unique to DPA, giving DBAs unmatched flexibility to trigger Alerts on any condition for which a SQL statement or stored procedure can be constructed.
  • Custom alerts are designed to give the DBA the ability to check for conditions outside the normal bounds of pre-defined DPA alerts.
  • Alerts can be provided via standard email, pagers, and cell phone text messaging. They can also be integrated with enterprise monitoring tools via SNMP.

Benefits Overview

Only DPA combines the best of three sources of Alert information: DPA Wait-time data, existing database vendor supplied statistics and custom SQL statements or stored procedures.  Designed for any level DBA, they can generate Alerts at multiple severity levels based on numeric ranges, text values or true/false results from the custom code. This gives the DBA advance notice of a condition that affects a specific user community, and the ability to make changes before the users are impacted.

  • Custom DPA Alerts give DBAs proactive control of their database, notifying them of issues before they become problems for database customers.
  • Custom Alerts allow DBAs to create Alerts on their own PL/SQL queries, giving flexibility to Alert on unique information of interest to the expert DBA.
  • The result is improved customer service, fewer trouble tickets, and increased compliance with database SLAs.


  • DPA, all versions


Custom Stored Procedure Alerts

Custom stored procedures can be added to the database that holds the DPA Performance Data Warehouse. It will be run from within the Performance Data Warehouse, (as the DPA admin user), so the DPA admin user needs visibility and privileges to execute the custom code. Once you have written the stored procedure and have added it to the database, use a call to the stored procedure in the Create/Modify Alert screen making sure the Alert Category field has "Procedure" selected.

Required Stored Procedure Parameters

Your custom stored procedure may include any number of parameters, but it must include exactly 2 output parameters (the alert will break if there are more than 2 output parameters), and in exactly this order relative to each other, (i.e. other parameters can be intermixed, but these 2 must be in this relative order):

  1. AlertValue OUT VARCHAR2
  2. AlertString OUT VARCHAR2

AlertValue is the result of the stored procedure conforming to the Return Type that you specify on the DPA Create/Modify Alert screen:

Return Type Description
'Info', 'Low', 'Medium', 'High' The return value is case-insensitive, so returning ‘low’ is treated the same as ‘Low’. Also, returning the first letter of the level name is sufficient, so ‘I’, ‘L’, ‘M’ and ‘H’ are valid.
Number Fractional and negative numbers are valid. Note that the numeric value is returned in the AlertValue of type VARCHAR2.

The value of 'TRUE' (case insensitive) signals that the desired condition has been met, causing the alert level to be set to the level specified on the Create/Modify Alert screen. AlertString is defined as the result of the stored procedure. The value of this will be sent as the content or body of the alert and can contain anything allowed by the local SMTP or SNMP servers. 

Both AlertValue and AlertString are specified using Custom Tags (see discussion below). For Oracle databases, a very useful custom tag for stored procedures is #DBLINK#. It is a placeholder for the database link used to query the monitored database. It must be specified in single quotes and passed into the stored procedure as a VARCHAR2 input parameter.

Note: In DPA, database links must be created and maintained by the DBA group. 

Here is an example of a stored procedure call using custom parameters:

myproc('param1', #ALERTVALUE#, 100, #ALERTSTRING#, '#DBLINK#')

The corresponding stored procedure definition could look like:

procedure myproc(myp1 IN VARCHAR2,
                alertvalue OUT VARCHAR2,
                myp3 IN NUMBER,
                alertstring OUT VARCHAR2,
                dblink IN VARCHAR2 )

The stored procedure could then make use of these values. The following is a coding example that passes back a Return Type (i.e., alertvalue) of ‘TRUE’ or ‘FALSE’:

-- Assume the alert condition is not met

alertvalue := 'FALSE';
alertstring := '';

-- Query based on input values
'select x from myschema.mytable@' || dblink
|| ' WHERE y = ' || myp1
INTO myvar;

-- Test the desired condition
IF myvar > myp3 THEN

-- Condition has been met
alertvalue := 'TRUE';
alertstring := 'My condition met: value = ' || myvar;

Stored Procedures Errors

Your custom stored procedure may produce expected or unexpected errors. Errors raised from your stored procedure result in the alert level being set to Broken. The error text is available on the Alert History screen accessible from the History link on the main Alerts page. To avoid errors while writing and debugging your stored procedure, do not activate the alert until it works the way you want. Also, test the alert (via the Test Alert button on the Create/Modify Alert screen) to determine if an error or the desired results are produced.

Custom SQL Statement Alerts

You can create two types of Custom SQL Statement Alerts:

  • Single Return Type – The SQL statement must return one row and one column of numeric data. An example is a query that retrieves the current value of a database parameter.
  • Multiple Return Type – The SQL statement can return many rows of data with two paired value columns of data. An example is a query that returns the amount of free space for several tablespaces (Oracle) or databases (SQL Server) and looks similar to the following:
    • APP1     500
    • APP2     760
    • APP3     120
    • APP4     5

You can also use custom tags in your SQL statements. A common used for Oracle custom alerts is #DBLINK# which enables querying of the monitored database. For example:

SELECT myvalue FROM myschema.mytable@#DBLINK#

An example of a custom SQL statement can be found by selecting the “% Redo Logs Unarchived” alert.

SQL Statement Errors

Errors produced by your SQL statement will result in the alert’s level being set to Broken, just as occurs with Stored Procedure alerts. See the Stored Procedure Errors section above for more details.

Custom Tags

To provide additional capabilities to your custom stored procedures and SQL statements, custom tags have been provided. You can embed custom tags in your statement or procedure call, which are replaced by the DPA alerting engine with the appropriate runtime value. Valid custom tags are shown below.

Custom tag Description

The internal DPA ID for the monitored database.

Data Type: VARCHAR2(50)


Database link used to connect to the monitored database.

Data Type: VARCHAR2(50)


Required output VARCHAR2 parameter for stored procedures (not used in SQL Statements). On return from the stored procedure, it contains a value corresponding to the Return Type. Must appear in the parameter list before #ALERTSTRING#.

Data Type: VARCHAR2(500)


Required output VARCHAR2 parameter for stored procedures (not used in SQL Statements). On return from the stored procedure, it contains a description of the alert condition.

Data Type:  VARCHAR2(4000)


Optional input parameter to your SQL statement or stored procedure. It contains the value, in minutes, for the Run Alert Every field on the Create/Modify Alert screen.

Data Type: NUMBER


  • SQL Statement: select col from dpa_table where dbid=#DBID#
  • Stored Procedure: myproc(…,#DBID#, …)
  • SQL Statement: select mycol from myschema.mytable@#DBLINK#
  • Stored Procedure: myproc(…,'#DBLINK#', …)
    myproc(…, #ALERTVALUE#, …)
    Comment If Return Type is set to ‘Number’, the maximum value is defined by the valid range for type NUMBER in Oracle (as defined on the database that holds the Performance Data Warehouse).
    myproc(…, #ALERTSTRING#, …)
  • SQL Statement: select mycol from myschema.mytable@#DBLINK#  where mydate > SYSDATE – (#FREQUENCY#/1440)
  •  Stored Procedure: myproc(…, #FREQUENCY#, …) 
    (Note: Other custom tags exist for DPA internal use only.)
Last modified