Oct 202014
 

Example 1: For Sending Email to single recipient from Oracle database follow following steps:

Step 1: Compile below “Send_Mail” Procedure.

CREATE OR REPLACE PROCEDURE send_mail (p_to  IN VARCHAR2,

p_from     IN VARCHAR2,

p_subject   IN VARCHAR2,

p_html_msg IN VARCHAR2 DEFAULT NULL,

 p_smtp_host IN VARCHAR2,

p_smtp_port IN NUMBER DEFAULT 25)

AS

l_mail_conn   UTL_SMTP.connection;

l_boundary   VARCHAR2(50) := ‘—-=*#abc1234321cba#*=’;

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, ‘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_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=”iso-8859-1″‘ || 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);

END send_mail;

/

Step 2: Compile Below “Send_Mail_Proc” Procedure.

Required to change “P_FROM”, “P_TO” , “P_SMTP_HOST”, “P_SUBJECT” and “P_HTML_MSG” Parameters.

CREATE OR REPLACE procedure SEND_MAIL_PROC

AS

P_TO VARCHAR2(32767);

P_FROM VARCHAR2(32767);

P_SUBJECT VARCHAR2(32767);

P_HTML_MSG VARCHAR2(32767);

P_SMTP_HOST VARCHAR2(32767);

P_SMTP_PORT NUMBER;

P_COUNT NUMBER;

     BEGIN

     P_FROM := ‘from@mycompany.com’;  

     P_TO := ‘To@tocompany.com’;  

     P_SMTP_HOST := ‘MAIL.mycompany.com’;

     P_SMTP_PORT := 25;  

     P_SUBJECT := ‘TEST MAIL’;

     P_HTML_MSG:=’Number of Records found on ‘|| TO_CHAR((SYSDATE), ‘DD-MON-YYYY HH12:MI:SS’) || ‘  is : ‘ ; 

     select count(*) INTO P_COUNT from TAB; 

     P_HTML_MSG:=P_HTML_MSG|| P_COUNT;              

       SEND_MAIL ( P_TO, P_FROM, P_SUBJECT, P_HTML_MSG, P_SMTP_HOST, P_SMTP_PORT );

END SEND_MAIL_PROC;

/

 

Step 3: Execute the above procedure “Send_Mail_Proc” to send the Email.

Begin

SEND_MAIL_PROC;

End;

Example 2: For Sending Email to multiple recipients from Oracle database follow following steps:

Step 1: Compile below “Send_Mail” Procedure.

CREATE OR REPLACE PROCEDURE send_mail (p_to       IN VARCHAR2,

                                       p_from     IN VARCHAR2,

                                       p_subject   IN VARCHAR2,                                    

                                       p_html_msg IN VARCHAR2 DEFAULT NULL,

                                       p_smtp_host IN VARCHAR2,

                                      p_smtp_port IN NUMBER DEFAULT 25)

AS

l_mail_conn   UTL_SMTP.connection;

l_boundary   VARCHAR2(50) := ‘—-=*#abc1234321cba#*=’;

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, ‘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_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=”iso-8859-1″‘ || 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);

END send_mail;

/

Step 2: Compile below “Split” Function:

CREATE OR REPLACE function split

(

p_list varchar2,

p_del varchar2 := ‘,’

) return split_tbl pipelined

is

l_idx   pls_integer;

l_list   varchar2(32767) := p_list;

l_value   varchar2(32767);

begin

loop

l_idx := instr(l_list,p_del);

if l_idx > 0 then

pipe row(substr(l_list,1,l_idx-1));

l_list := substr(l_list,l_idx+length(p_del));

else

pipe row(l_list);

exit;

end if;

end loop;

return;

end split;

/

 

Step 3: Compile Below “Send_Mail_Proc” Procedure.

Required to change “P_FROM”, “P_TO” , “P_SMTP_HOST”, “P_SUBJECT” and “P_HTML_MSG” Parameters.

CREATE OR REPLACE procedure GSMCDR.SEND_MAIL_PROC(

P_RECIPIENT in VARCHAR2

)

AS 

P_TO VARCHAR2(32767);

P_FROM VARCHAR2(32767);

P_SUBJECT VARCHAR2(32767);

P_HTML_MSG VARCHAR2(32767);

P_SMTP_HOST VARCHAR2(32767);

P_SMTP_PORT NUMBER;

P_COUNT NUMBER;

     BEGIN

     P_TO := P_RECIPIENT;

     P_FROM := ‘from@mycompany.com’;  

     P_SMTP_HOST := ‘MAIL.mycompany.com’;

     P_SMTP_PORT := 25;  

     P_SUBJECT := ‘TEST MAIL’;

     P_HTML_MSG:=’Number of Records found on ‘|| TO_CHAR((SYSDATE), ‘DD-MON-YYYY HH12:MI:SS’) || ‘is : ‘ ; 

     select count(*) INTO P_COUNT from TAB; 

     P_HTML_MSG:=P_HTML_MSG|| P_COUNT;               

       FOR REC in (select COLUMN_VALUE from table(split(P_RECIPIENT)))

         LOOP        

         begin

         SEND_MAIL ( REC.COLUMN_VALUE||”, P_FROM, P_SUBJECT, P_HTML_MSG, P_SMTP_HOST, P_SMTP_PORT );

         End; 

       End Loop; 

END SEND_MAIL_PROC;

/

Step 4: Execute the above procedure “Send_Mail_Proc” to send the mail.

Begin

SEND_MAIL_PROC (‘Reci1.Tocompany.com, ‘Reci2.Tocompany.com’);

End;

 

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)