Send emails from the database
                                                 Last update (2009-03-31 15:23:13)
                                                                                                                   Date added (2008-02-15 11:12:32)

Summary
A simple procedure to send emails from the database. It works for Oracle9i and afterwards. You need to know the IP of the SMTP email server.

CREATE OR REPLACE PROCEDURE send_mail(sender IN VARCHAR2,
recipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2)
IS

mail_conn utl_smtp.connection;
test varchar2(100);
crlf varchar2(2):= CHR( 13 ) || CHR( 10 );
mesg varchar2(2000);

BEGIN

mail_conn := utl_smtp.open_connection('10.101.12.36');
utl_smtp.helo(mail_conn, 'maria.research.com');
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);

mesg:= subject || ' ' || crlf || message || crlf;
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
END;
/

For example login to sqlplus and run

BEGIN
Send_Mail('skaparelis@ote.gr','skaparelis@ote.gr', 'tablespace error', 'You need to add more space!!!!');
END;
/

You can also setup an emailing list, a simple table with the email of the recipients.

CREATE TABLE HELPDESK.S_RECIPIENTS
(
  RECIPIENT   VARCHAR2(100)
)
TABLESPACE HELPDESK
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;

INSERT INTO HELPDESK.S_RECIPIENTS VALUES ('skaparelis@ote.gr');
INSERT INTO HELPDESK.S_RECIPIENTS VALUES ('kaparelis@ote.gr');
COMMIT;