Send HTML mail through Oracle in multi language
So lets start....
1. First you need to check if your oracle database has these two packages or not if not than install it.
Install utlmail.sql :- this is the package responsible for sending mail through mail.
sqlplus sys/<pwd> SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
2. Now if you have package installed in your database, than check the database language it should be
AL32UTF8.
In most of the database UTL_SMTP package is already installed and database language is also predefined to AL32UTF8 ,I am telling you this to be on the safe side and everything should work.
3. So we are ready to go , below you will find two function which will send mail to user.
htmlcall, send_mail_function
htmlcall----- it will frame html message in a way you like in any language you want.
send_mail_function --- it will send mail to user.
4. Function which will send mail is : Explanation is given below
create or replace FUNCTION send_mail_function(
p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN NVARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_html_msg IN VARCHAR2 DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
RETURN BOOLEAN
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
set_error boolean;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.WRITE_DATA(l_mail_conn, 'MIME-Version: 1.0'|| UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(l_mail_conn, 'Content-Type: text/html;charset=tis620'||UTL_TCP.CRLF);
UTL_SMTP.write_raw_data(l_mail_conn,utl_raw.cast_to_raw('Subject:' ||p_subject||UTL_TCP.CRLF));
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn,'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="UTF-8"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
IF p_html_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="UTF-8"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_html_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
RETURN FALSE;
END;
/
Here
send_mail_function is a function name and i am returning Boolean from the function so if mail send than it will return true or otherwise it will return false, that you can handle in your code and do your stuff as you like to do.
given parameter will be passed when you call the function, that will be given below.
p_to ------- to whom you want to send mail.
p_from ------- from the user you want to send the mail it will be anything like:xyz@you.com. p_subject ----- subject of mail.
p_text_msg ------- "" // leave it blank
p_html_msg ------
p_smtp_host ------ host name smtp server name.
p_smtp_port -------25.
please note that : for sending mail in different language MIME type should be correct that is charset=tis620 if it is not working change the MIME type and try again, In my case it is working.
5. Second Function: htmlcall ----which will frame mail and return a mail which will send by send_mail_function.
create or replace FUNCTION htmlcall( parameter1 IN varchar2,
parameter2 IN varchar2,
)
RETURN VARCHAR2 IS
L_HTML_Call varchar2(32767);
BEGIN
L_HTML_Call := '<html>
<head>
<title></title>
</head>
<body>
<p>Dear '||Parameter1||', </p>
<p>body.</p>
<p>body -- you can also frame table which will pick value from database:</br>
<table>
<tr>
<td>parameter2namegivenhere</td>
<td>'||parameter2||'</td>
</tr>
<tr>
<td>parameter2namegivenhere</td>
<td>'||parameter2||'</td>
</tr>
</table>
</body>
</html>';
RETURN L_HTML_Call;
END htmlcall;
/
parameter1, parameter2 are the parameters from the database as i want some value from the database.
if you dont want than remove it.
it will return L_HTML_Call which will carry a mail you have to have same Nvarchar(32767) parameter to hold this value on the other side.
So the Final Steps are :-
1. first call htmlcall by passing parameter (optional).
2. Mail framed and hold it on other side like this:
p_html_msg_value:= htmlcall('parameter1', 'parameter2');
3.Some_value contain mail now call send_mail_function to send mail to user.
mail_result := send_mail_function(
p_to => 'nav@xyz.com',
p_from => 'anyname@xyx.com',
p_subject => 'subject',
p_text_msg => '',
p_html_msg => p_html_msg_value,
p_smtp_host => 'hostname.xyx.com',
p_smtp_port => 25);
mail_result will hold true or false so if you want you can perform any other action.
for you....
Kindly Bookmark this Post using your favorite Bookmarking service:
Post a Comment
Note: only a member of this blog may post a comment.