How to send mail when cursor limit exceeds (ORA-01000)

This is based on one of my posting in OTN. We will use AFTER SERVERERROR ON schema trigger along with UTL_SMTP to send mail.

SQL> conn scott@orclsb
Enter password: *****
Connected.
SQL>
SQL> /** The databse version in which this executed **/
SQL>
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> /** This package will be used for generating the
SQL>     ORA-0100: Maximum open cursors exceeded error **/
SQL> CREATE OR REPLACE PACKAGE test_open_cursor  AS
  2    cursor c01 is select * from dual;
  3    cursor c02 is select * from dual;
  4    cursor c03 is select * from dual;
  5    cursor c04 is select * from dual;
  6    cursor c05 is select * from dual;
  7    cursor c06 is select * from dual;
  8    cursor c07 is select * from dual;
  9    cursor c08 is select * from dual;
 10    cursor c09 is select * from dual;
 11    cursor c10 is select * from dual;
 12    cursor c11 is select * from dual;
 13    cursor c12 is select * from dual;
 14    cursor c13 is select * from dual;
 15    cursor c14 is select * from dual;
 16    cursor c15 is select * from dual;
 17    cursor c16 is select * from dual;
 18    cursor c17 is select * from dual;
 19    cursor c18 is select * from dual;
 20    cursor c19 is select * from dual;
 21    cursor c20 is select * from dual;
 22    cursor c21 is select * from dual;
 23    cursor c22 is select * from dual;
 24    cursor c23 is select * from dual;
 25    cursor c24 is select * from dual;
 26    cursor c25 is select * from dual;
 27    cursor c26 is select * from dual;
 28    cursor c27 is select * from dual;
 29    cursor c28 is select * from dual;
 30    cursor c29 is select * from dual;
 31    cursor c30 is select * from dual;
 32    cursor c31 is select * from dual;
 33    cursor c32 is select * from dual;
 34    cursor c33 is select * from dual;
 35    cursor c34 is select * from dual;
 36    cursor c35 is select * from dual;
 37    cursor c36 is select * from dual;
 38    cursor c37 is select * from dual;
 39    cursor c38 is select * from dual;
 40    cursor c39 is select * from dual;
 41    cursor c40 is select * from dual;
 42  END;
 43  /

Package created.

SQL> /** This procedure will be called from the trigger
SQL>     to send the mail. **/
SQL> CREATE OR REPLACE PROCEDURE send_cursor_mail AUTHID CURRENT_USER IS
  2    /*** UTL_SMTP related varriable ***/
  3    v_connection_handle  UTL_SMTP.connection;
  4    v_from_email_address VARCHAR2(30) := 'dabase_admin@oracle.com';
  5    v_to_email_address   VARCHAR2(30) := 'xyz@gmail.com';
  6    v_smtp_host          VARCHAR2(30) := 'x.xxx.xxx.xxx'; --My mail server, replace it with yours
.
  7    v_subject            VARCHAR2(300) := 'ORA-0100: Maximum open cursors exceeded';
  8    l_message            VARCHAR2(32767) := 'ORA-0100: Maximum open cursors exceeded';
  9    crlf CONSTANT VARCHAR2(2):=CHR(13) || CHR(10);
 10    /* This send_header procedure is written in the documentation */
 11    PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
 12    BEGIN
 13      UTL_SMTP.WRITE_DATA(v_connection_handle,
 14                          pi_name || ': ' || pi_header || crlf);
 15    END;
 16  BEGIN
 17    v_connection_handle := UTL_SMTP.open_connection(v_smtp_host);
 18    UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
 19    UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
 20    UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
 21    UTL_SMTP.OPEN_DATA(v_connection_handle);
 22 
 23    send_header('From', '"Sender" <' || v_from_email_address || '>');
 24    send_header('To', '"Recipient" <' || v_to_email_address || '>');
 25    send_header('Subject', v_subject);
 26    UTL_SMTP.write_data(v_connection_handle, crlf || l_message);
 27    UTL_SMTP.close_data(v_connection_handle);
 28    UTL_SMTP.quit(v_connection_handle);
 29  END;
 30  /

Procedure created.

SQL> /** The trigger: This will capture the error and
SQL>     send a mail **/
SQL> CREATE OR REPLACE TRIGGER open_cur_servererr
  2    AFTER SERVERERROR ON schema
  3  BEGIN
  4    IF ora_is_servererror(1000) THEN
  5      DBMS_OUTPUT.put_line('ORA-0100: Captured in trigger');
  6      send_cursor_mail; --send the mail
  7      DBMS_OUTPUT.put_line('Mail sent to Saubhik');
  8    END IF;
  9  END;
 10  /

Trigger created.

SQL> SET SERVEROUT ON
SQL>
SQL> /** Generate the error */
SQL> BEGIN
  2    FOR i in 1 .. 40 LOOP
  3      execute immediate 'begin open test_open_cursor.c' || to_char(i, 'fm00') ||
  4                        '; end;';
  5    END LOOP;
  6  END;
  7  /
ORA-0100: Captured in trigger
Mail sent to Saubhik
BEGIN
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.TEST_OPEN_CURSOR", line 30
ORA-06512: at line 1
ORA-06512: at line 3


SQL> 

Look at the lines ORA-0100: Captured in trigger, Mail sent to Saubhik
and I have received the mail in my mail address specified in the procedure.
Another good example of this types of triggers can be found  here on OTN.