A simple procedure to send emails from the database.
You just need to know the IP of the SMTP email server.
CREATE OR REPLACE PROCEDURE mail_send(sender IN VARCHAR2,
recipient IN VARCHAR2, subject IN VARCHAR2, message IN VARCHAR2) IS mail_conn utl_smtp.connection; test varchar2(200); crlf varchar2(2):= CHR( 13 ) || CHR( 10 ); mesg varchar2(2000); BEGIN mail_conn := utl_smtp.open_connection('60.212.02.34'); utl_smtp.helo(mail_conn, 'albert.einstein.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 mail_send('spiderman@dbc.com','ospiderman@dbc.com', '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(200)
)
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 ('spiderman@dbc.com');
INSERT INTO HELPDESK.S_RECIPIENTS VALUES ('ospiderman@dbc.com');
COMMIT;
No comments:
Post a Comment