Pages

Search

Sunday, November 2, 2008

How to send mail in Oracle

This function accepts destination mail ID,Mail subject,Mail body.
This uses UTL_SMTP package (In built) to handle process of sending mails.
CREATE OR REPLACE FUNCTION Sendmail
(
MAILID IN VARCHAR,
MAILSUB IN VARCHAR,
MAILBODY IN VARCHAR
) RETURN NUMBER
AS
--TO GET REQUIRED DETAILS FROM CONFIGURATION DETAILS TABLE FOR REQUIRED PARAMETERS
L_MAILHOST VARCHAR2(64) := Your Mail Server IP;
L_FROM VARCHAR2(64) := From Mail ID;
L_PORT NUMBER := Your Mail server Port Number;
TEMPVALUE VARCHAR(2000);
L_MAIL_CONN UTL_SMTP.CONNECTION;
TEMPCOLNAME USER_TAB_COLS.COLUMN_NAME%TYPE;
V_REPLY UTL_SMTP.REPLY;
TEMPCURSOR SYS_REFCURSOR;
BEGIN
--ESTABLISHES A CONNECTION TO MAIL SERVER WITH MENTIONED IP AND SENDS MAIL WITH MENTIONED SUBJECT AND BODY
L_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(L_MAILHOST,L_PORT);
V_REPLY:=UTL_SMTP.HELO(L_MAIL_CONN, L_MAILHOST);
UTL_SMTP.MAIL(L_MAIL_CONN, L_FROM);
UTL_SMTP.RCPT(L_MAIL_CONN, MAILID);
UTL_SMTP.OPEN_DATA(L_MAIL_CONN);
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'DATE: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'FROM: ' || L_FROM || CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'SUBJECT: '||MAILSUB||' '||CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'TO: ' || MAILID || CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, '' || CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, ''
||CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, ' '||MAILBODY||'. '||CHR(13));
UTL_SMTP.CLOSE_DATA(L_MAIL_CONN);
UTL_SMTP.QUIT(L_MAIL_CONN);
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN -1;
--RETURNING VALUE BASED ON EXECUTION
END;
/

No comments:

Post a Comment