9/09/2017

Oracle how to Send emails from the database

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

Popular Posts