1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

How to send email using UTL_SMTP package from Oracle database

CREATE OR REPLACE PROCEDURE mailfromdb (MESSAGE IN VARCHAR)
AS
CURSOR c1
IS
SELECT mailid.send_recpt, email_address, order_no
FROM mailid, mailid_order
WHERE mailid.send_recpt = mailid_order.send_recpt
ORDER BY order_no;

conn UTL_SMTP.connection;
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
mesg VARCHAR2 (4000);
mesg_body VARCHAR2 (4000);
adminmailid VARCHAR2 (30);
vtolist VARCHAR2 (2000);
vcclist VARCHAR2 (2000);
vbcclist VARCHAR2 (2000);
vsenderemail VARCHAR2 (2000);
BEGIN
/* Open connection */
conn := UTL_SMTP.open_connection ('smtp.techfaq360.com', 25);
/* Hand Shake */
UTL_SMTP.helo (conn, 'smtp.techfaq360.com');

/* Loop for configure sender and recipient to UTL_SMTP */
FOR c1rec IN c1
LOOP
IF c1rec.send_recpt = 'SENDER'
THEN
UTL_SMTP.mail (conn, c1rec.email_address);
vsenderemail := c1rec.email_address;
ELSE
UTL_SMTP.rcpt (conn, c1rec.email_address);
END IF;

/* Making a TO list */
IF UPPER (c1rec.send_recpt) = 'TO'
THEN
vtolist := vtolist || c1rec.email_address || ';';
END IF;

/* Making a CC list */
IF UPPER (c1rec.send_recpt) = 'CC'
THEN
vcclist := vcclist || c1rec.email_address || ';';
END IF;

/* Making a BCC list */
IF UPPER (c1rec.send_recpt) = 'BCC'
THEN
vbcclist := vbcclist || c1rec.email_address || ';';
END IF;
END LOOP;

/* making a message buffer */
mesg :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf
|| 'From: '
|| vsenderemail
|| crlf
|| 'Subject: Mail Through ORACLE Database'
|| crlf
|| 'To: '
|| vtolist
|| crlf
|| 'Cc: '
|| vcclist
|| crlf
|| 'Bcc: '
|| vbcclist
|| crlf
|| crlf
|| ' This is Mail from Oracle Database By Using UTL_SMTP Package'
|| crlf
|| 'It is very easy to configure Tell me if you face any problems'
|| crlf
|| MESSAGE;
/* Configure sending message */
UTL_SMTP.DATA (conn, mesg);
/* closing connection */
UTL_SMTP.quit (conn);
END;
/

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy