@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
Grant execute on UTL_MAIL to public;
ALTER SYSTEM SET smtp_out_server = ‘mailhost’ scope=both;
For Example:
BEGIN
UTL_MAIL.send(sender => ‘[email protected]’,
recipients => ‘[email protected]’,
subject => ‘Test Mail’,
message => ‘Everythings is OK’,
mime_type => ‘text; charset=us-ascii’);
END;
/
Note: You must have an outgoing SMTP server IP to configure sending mail from the database.
Oracle allows you to send mail using procedure may be just message or message from tables.
CREATE TABLE EMP
(
ENO NUMBER(4),
ENAME VARCHAR2(50),
BASIC NUMBER(10,2));
CREATE OR REPLACE PROCEDURE SENDMAIL(TOO IN VARCHAR, FROMM IN VARCHAR, SUBJECT IN VARCHAR, BODY IN VARCHAR) AS
SMTP_HOST VARCHAR2(50) :=’212.12.164.2′;
PORT varchar2(2) :=’25’;
CONN UTL_SMTP.CONNECTION;
BEGIN
CONN:=UTL_SMTP.OPEN_CONNECTION(‘212.12.164.2′, ’25’);
UTL_SMTP.HELO(CONN,’212.12.164.2′);
UTL_SMTP.MAIL(CONN,’[email protected]’);
UTL_SMTP.RCPT(CONN,’[email protected]’);
UTL_SMTP.DATA(CONN,’Test’);
UTL_SMTP.QUIT(CONN);
END;
/
CREATE OR REPLACE TRIGGER EMP_TRIGGER AFTER INSERT ON EMP
FOR EACH ROW
BEGIN
sendmail(‘[email protected]’,’[email protected]’,’Test’,:new.ename);
END;
Desc emp;
insert into emp values (4, ‘ahmed’, 400);
The Above procedure will send a mail from oracle. When you call this procedure we need to pass To/From Address , Subject and Body of the message. For Example
To_address:[email protected]
From_address: [email protected]
Subject and Body of the Message: Test
To Test this Procedure i created a temp table Emp with fields eno number(4), ename varchar2(50), basic number(10,2). Our aim is whenever some one insert record into emp table it needs to send an email with the Ename he entered. So we created Trigger after insert record into Emp table Call mail sending procedure with Employee name.
