Sending Emails through Oracle procedure

This procedure was tested in oracle9i. This procedure has ability to send the email TO/CC/BCC.

Before implementing this procedure, have the Email server host name handy. You can find the email server host name from your email software(lotus notes, outlook express etc). You can also check your admin or help desk to find out the email server host name.

There are only four step to implement this email procedure. Just one note, i have used pipe symbol in the plsql procedure. But it is not visible in the blog. So i used ### symbol where ever, there is the pipe sign. So please replace the ### with pipe symbol in the code.


Step 1

Create the table to store the email address.

CREATE TABLE EMAIL_ADDRESS(
EMAIL VARCHAR2(100),
EMAIL_GROUP VARCHAR(20))
/

We can store the emails on different group. In this example, we use DBA, ADMIN, BUSIENSS, MANAGERS. We can add these groups on TO, CC, BCC based on our requirements.

Here is the sample insert statement to store the email address.

insert into email_address values('scott@xyzcomp.com','DBA');
insert into email_address values('robert@xyzcomp.com','DBA');
insert into email_address values('mat@xyzcomp.com','ADMIN');
insert into email_address values('lissa@xyzcomp.com','ADMIN');
insert into email_address values('herman@xyzcomp.com','BUSINESS');
insert into email_address values('martin@xyzcomp.com','BUSINESS');
insert into email_address values('david@xyzcomp.com','MANAGER');
insert into email_address values('nany@xyzcomp.com','MANAGER');


Step2
create the array to store the email address

CREATE OR REPLACE type group_array as table of varchar2(255)
/

Step3

Here is the content of email package. Create this package..

-- The piple symbol is not displaying in the blog. So i placed ### symbol
-- whereever there is a pipe symbol. If we want to test the script in your test database,
-- please replace the ### symbol with pipe symbol.

create or replace PACKAGE EMAIL_PACKAGE IS

function address_email( p_string in varchar2,
p_recipients in group_array ) return varchar2;

procedure send( p_sender_email in varchar2,
p_from in varchar2,
p_to in group_array default group_array(),
p_cc in group_array default group_array(),
p_bcc in group_array default group_array(),
p_subject in varchar2,
p_body in long);

PROCEDURE LISTEMAILS(P_ARRAY_TO_GROUP IN group_array,
P_ARRAY_CC_GROUP IN group_array ,
P_ARRAY_BCC_GROUP IN group_array,
l_data_to OUT group_array,
l_data_cc OUT group_array,
l_data_bcc OUT group_array);

END EMAIL_PACKAGE;
/

create or replace package body EMAIL_PACKAGE as
g_crlf char(2) default chr(13)###chr(10);
g_mail_conn utl_smtp.connection;
g_mailhost varchar2(255) := 'EMAIL_HOST_NAME';

FUNCTION address_email( p_string in varchar2,
p_recipients in group_array ) return varchar2
is
l_recipients long;
begin
for i in 1 .. p_recipients.count
loop
utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
if ( l_recipients is null )
then
l_recipients := p_string ### p_recipients(i) ;
else
l_recipients := l_recipients ### ', ' ### p_recipients(i);
end if;
end loop;
return l_recipients;
end;

procedure send( p_sender_email in varchar2,
p_from in varchar2,
p_to in group_array default group_array(),
p_cc in group_array default group_array(),
p_bcc in group_array default group_array(),
p_subject in varchar2,
p_body in long) IS
l_to_list long;
l_cc_list long;
l_bcc_list long;
l_date varchar2(255) default
to_char( SYSDATE, 'dd Mon yy hh24:mi:ss' );

procedure writeData( p_text in varchar2 )
as
begin
if ( p_text is not null )
then
utl_smtp.write_data( g_mail_conn, p_text ### g_crlf );
end if;
end;

BEGIN
g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);
utl_smtp.helo(g_mail_conn, g_mailhost);
utl_smtp.mail(g_mail_conn, p_sender_email);
l_to_list := address_email( 'To: ', p_to );
l_cc_list := address_email( 'Cc: ', p_cc );
l_bcc_list := address_email( 'Bcc: ', p_bcc );
utl_smtp.open_data(g_mail_conn );

writeData( 'Date: ' ### l_date );
writeData( 'From: ' ### nvl( p_from, p_sender_email ) );
writeData( 'Subject: ' ### nvl( p_subject, '(no subject)' ) );

writeData( l_to_list );
writeData( l_cc_list );

utl_smtp.write_data( g_mail_conn, '' ### g_crlf );
utl_smtp.write_data(g_mail_conn, p_body );
utl_smtp.close_data(g_mail_conn );
utl_smtp.quit(g_mail_conn);
END send;

PROCEDURE LISTEMAILS(P_ARRAY_TO_GROUP IN group_array ,
P_ARRAY_CC_GROUP in group_array ,
P_ARRAY_BCC_GROUP in group_array ,
l_data_to out group_array ,
l_data_cc out group_array ,
l_data_bcc out group_array) as

CURSOR C1 IS SELECT * FROM EMAIL_ADDRESS;

BEGIN

l_data_to := group_array();
l_data_cc := group_array();
l_data_bcc := group_array();


FOR X IN 1..P_ARRAY_TO_GROUP.COUNT LOOP

FOR I IN C1 LOOP

IF I.EMAIL_GROUP = P_ARRAY_TO_GROUP(X) THEN
l_data_to.extend ;
l_data_to(l_data_to.count) := I.EMAIL;
END IF;
END LOOP;

END LOOP;


FOR X IN 1..P_ARRAY_CC_GROUP.COUNT LOOP

FOR I IN C1 LOOP

IF I.EMAIL_GROUP = P_ARRAY_CC_GROUP(X) THEN
l_data_cc.extend ;
l_data_cc(l_data_cc.count) := I.EMAIL;
END IF;
END LOOP;

END LOOP;

FOR X IN 1..P_ARRAY_BCC_GROUP.COUNT LOOP

FOR I IN C1 LOOP

IF I.EMAIL_GROUP = P_ARRAY_BCC_GROUP(X) THEN
l_data_bcc.extend ;
l_data_bcc(l_data_bcc.count) := I.EMAIL;
END IF;
END LOOP;

END LOOP;



END LISTEMAILS;
END EMAIL_PACKAGE;
/


Step4
Here is the sample unnamed plsql code to send the email.

-- The piple symbol is not displaying in the blog. So i placed ### symbol
-- whereever there is a pipe symbol. If we want to test the script in your test database,
-- please replace the ### symbol with pipe symbol.

declare
to_group group_array DEFAULT group_array();
cc_group group_array DEFAULT group_array();
bcc_group group_array DEFAULT group_array();
r_to_group group_array DEFAULT group_array();
r_cc_group group_array DEFAULT group_array();
r_bcc_group group_array DEFAULT group_array();
v_message VARCHAR2(22767) := 'test';
v_crlf VARCHAR2(2) := CHR(13) ### CHR(10);
v_time NUMBER;
BEGIN

to_group.extend;
to_group(1) := 'DBA';

to_group.extend;
to_group(2) := 'BUSINESS';

cc_group.extend;
cc_group(1) := 'MANAGER';

bcc_group.extend;
bcc_group(1) := 'DBA PAGER';

Email_Package.listemails(to_group,cc_group,bcc_group,r_to_group,r_cc_group,r_bcc_group);
Email_Package.send
( p_sender_email => 'john.doe@xyz.com' ,
p_from => 'IT' ,
p_to => r_to_group ,
p_cc => r_cc_group ,
p_bcc => r_bcc_group ,
p_subject => 'Testing messaage' ,
p_body => v_message);
END;
/