Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 8 March 2014

How to send E-mail from Oracle database

sqlplus / as sysdba
@?/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 => 'shahid.ahmed@al-sadhan.com',
recipients => 'you@address.com',
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,'shahid.ahmedt@al-sadhan.com');
UTL_SMTP.RCPT(CONN,'xyz_abc@al-sadhan.com');
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('shahid.ahmedt@al-sadhan.com','xyz.abc@al-sadhan.com','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:shahid.ahmedt@al-sadhan.com
From_address: xyz.abc@al-sadhan.com
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
For More info:
If you face any issue related to ORA-29278: SMTP transient error: 421 Service not available: http://www.ora00600.com/scripts/databaseconfig/ORA-29278.html

0 comments:

Post a Comment