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;