***********************************************************
GRACE_KOEMAIL
************************************************************
create or replace PROCEDURE "GRACE_KOEMAIL" (
p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2)
is
l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_body_html clob := empty_clob; --This LOB will be the email message
l_offset number;
l_ammount number;
l_temp varchar2(32767) default null;
begin
l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
utl_smtp.helo( l_connection, p_smtp_hostname );
utl_smtp.mail( l_connection, p_from );
utl_smtp.rcpt( l_connection, p_to );
l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10);
----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary( l_body_html, false, 10 );
dbms_lob.write(l_body_html,length(l_temp),1,l_temp);
----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13)||chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
----------------------------------------------------
-- Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);
----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/html;' ||
chr(13) || chr(10) || chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);
----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(dbms_lob.substr(l_body_html,l_ammount,l_offset)));
l_offset := l_offset + l_ammount ;
l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
end loop;
utl_smtp.close_data(l_connection);
utl_smtp.quit( l_connection );
dbms_lob.freetemporary(l_body_html);
end;
*******************************************************
PROCSEND_BATCH_EMAIL
*******************************************************
create or replace PROCEDURE "PROCSEND_BATCH_EMAIL"
(Header in varchar2, fromDate in varchar2, toDate in varchar2, FromSender in varchar2,
ContentTxt in varchar2)
as
strSubject varchar2(200) := 'Grace Korean Church Member Information';
-- strSubject varchar2(200) := Header;
strPtext varchar2(100) := ' ';
strEmail varchar2(100);
strSTITLE varchar2(40);
-- strFromEmail varchar2(100) := 'gracechurch@grace.gov'; --'' ; --
strFromEmail varchar2(100) := 'Grace Ministies International <gracechurch@grace.gov>'; --'' ; --
strBody varchar2(4000);
emailSentFlag varchar2(3) := 'NO';
strDistrict varchar2(240);
strMemberName varchar(30);
--- strChurchSenior varchar2(60) :='Pastor Han, Gi Hong';
--- appLogon varchar2(100) :='http://localhost:8081/apex/f?p=130:2:2679667653831772::NO:::;
Test_count number := 1;
Test_Add number := 0;
EMAIL_CONTENT_T_SW number := 0;
START_EMAIL_OUT_ID number := 0;
lno varchar2(50);
MemberName varchar2(200);
dirName varchar(100);
begin
--DBMS_OUTPUT.PUT_LINE('start');
-- IF upper(P_IMEMBER_SK) = 'ALL' then
-- :P_IMEMBER_SK := '';
-- END IF;
for C1 in ( select IMEMBER_SK, STITLE_MAIL, SNATIVE_NM, SEMAILADDRESS from EMAIL_IN_T e, TITLE_T t
where e.STITLE = t.STITLE )
loop
strMemberName := c1.SNATIVE_NM;
strSTITLE := c1.STITLE_MAIL;
-- DBMS_OUTPUT.PUT_LINE(c1.SNATIVE_NM || ' PProcess' );
/* create html body */
strBody := '<font face=Arial><table border=0 width=90%><tr><td width=100%
colspan=2 height=19>
<p align=center><b><font face=Arial>'|| Header ||'
</font></b></P></td>
</tr>
<tr><td width=100% colspan=2 height=10>Date: ' ||
to_char(sysdate,'MM/DD/YYYY') || '
</td></tr>
<tr><td width=100% colspan=2 height=10> </td>
</tr>
<tr> <td>
' || 'From: ' || FromSender || ' </td> </tr>
<tr><td width=50% height=43><font face=Arial>
' || '¼£·Ò ! '||' '|| strMemberName || ' ' || strSTITLE|| '´Ô;'||' <br> '|| '</td> </tr>
<tr><td width=100% height=100 colspan=2> '|| ContentTxt || ' </span></span></td></tr></table></font> ';
/* get email addresses */
strEmail := c1.SEMAILADDRESS;
if strEmail is null then
null;
else
Test_Add := Test_Add + 1; --test
If Test_count = Test_Add then --test
--- GRACE_KOEMAIL (strEmail,strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);
--- GRACE_KOEMAIL ('pastorpghan@yahoo.com',strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);
--- GRACE_KOEMAIL ('luvluv916@gmail.com',strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);
--- GRACE_KOEMAIL ('haikoh4002@yahoo.com',strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);
emailSentFlag := 'YES';
end if; --test
INSERT INTO EMAIL_OUT_T (IMEMBER_SK, STITLE, SNATIVE_NM, SEMAILADDRESS, HEADER)
VALUES (c1.IMEMBER_SK, strSTITLE, strMemberName, strEmail, Header);
----Create content table only once-----
if EMAIL_CONTENT_T_SW = 0 then
SELECT EMAIL_OUT_SEQ.currval INTO START_EMAIL_OUT_ID FROM dual;
INSERT INTO EMAIL_CONTENT_T (EMAIL_CONTENT_ID, EMAIL_SENDER, HEADER, EMAIL_CONTENT)
VALUES (START_EMAIL_OUT_ID, FromSender, Header, ContentTxt);
EMAIL_CONTENT_T_SW := 1;
end if;
commit;
end if;
-- DBMS_OUTPUT.PUT_LINE('finished');
end loop;
End PROCSEND_BATCH_EMAIL;
*******************************************************
PROCSEND_KOEMAIL
*********************************************************
create or replace PROCEDURE "PROCSEND_KOEMAIL"
(STITLE_in in varchar2, fromDate in varchar2, toDate in varchar2)
as
strSubject varchar2(200) := 'MEMBER NOTIFICATION';
strPtext varchar2(100) := ' ';
strEmail varchar2(100);
strFromEmail varchar2(100) := 'gracechurch@grace.gov'; --'' ; --
strBody varchar2(4000);
Test_count number := 1;
Test_Add number := 0;
emailSentFlag varchar2(3) := 'NO';
strDistrict varchar2(240);
strMemberName varchar(30);
-- strChurchSenior varchar2(60) :='¢¯¢¯¢¯¢¯ ¢¯¢¯¢¯¢¯ ¢¯¢¯¢¯ ';
strChurchSenior varchar2(60) :='Pastor Han, Gi Hong';
--- appLogon varchar2(100) :='http://localhost:8081/apex/f?p=130:2:2679667653831772::NO:::;
myDocTypeID number;
lno varchar2(50);
MemberName varchar2(200);
dirName varchar(100);
begin
--DBMS_OUTPUT.PUT_LINE('start');
-- IF upper(P_IMEMBER_SK) = 'ALL' then
-- :P_IMEMBER_SK := '';
-- END IF;
for C1 in ( select SNATIVE_NM, SEMAILADDRESS
from TBLCHURCHMEMBER t
where GROUP_CELL_FLAG = 'Y'
AND t.SEMAILADDRESS IS NOT NULL
AND t.STITLE = STITLE_IN
AND t.IREGISTER_DT between (fromDate) and (toDate) )
loop
strMemberName := c1.SNATIVE_NM;
DBMS_OUTPUT.PUT_LINE(c1.SNATIVE_NM || ' PProcess' );
/* create html body */
strBody := '<font face=Arial><table border=0 width=90%><tr><td width=100%
colspan=2 height=19>
<p align=center><b><font face=Arial>Welcome to Grace Church<br>
Greetings Messages</font></b></td>
</tr><tr><td width=100% colspan=2 height=19> </td>
</tr><tr><td width=100% colspan=2 height=19>Date: ' ||
to_char(sysdate,'MM/DD/YYYY') || '
</font></td></tr><tr><td width=100% colspan=2 height=19> </td>
</tr><tr><td width=100% colspan=2 height=19> </td>
</tr><tr><td width=50% height=43><font face=Arial>' || 'TO: Dear '
|| strMemberName || '<br>' || '</td><td>' ||
'From: ' || strChurchSenior || ', <br> Senior Pastor of Grace Church</td>
</tr><tr><td width=50% height=18></td><td width=50% height=18></td>
<tr><td width=100% colspan=2 height=19>You are honor to our Grace Korean
Church. </font></td> </tr>
<tr><td width=100% colspan=2 height=19>We hope to see you again soon.
</font></td> </tr>
<tr><td width=100% height=129 colspan=2>May God Grace be with you and your Family and Every thing goes well with you..
<br/><b>Do not <u>reply to</u> this Email:</br><br/>
If this email does not belong to you please ignore this e-mail;
email notification to be stopped.<br/> Please give us chance to see you again.</span></span></td></tr></table></font> ';
/* get email addresses */
strEmail := c1.SEMAILADDRESS;
if strEmail is null then
null;
else
Test_Add := Test_Add + 1; --test
If Test_count = Test_Add then --test
-- GRACE_KOEMAIL (strEmail,strFromEmail,strSubject,' ',strBody,'localhost',2525);
-- GRACE_KOEMAIL ('pastorpghan@yahoo.com',strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);
-- GRACE_KOEMAIL ('luvluv916@gmail.com',strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);
GRACE_KOEMAIL ('haikoh4002@yahoo.com',strFromEmail,strSubject,' ',strBody,'relay.jangosmtp.net',2525);
emailSentFlag := 'YES';
end if; --test
INSERT INTO EMAIL_OUT_T ( SNATIVE_NM, SEMAILADDRESS, HEADER)
VALUES ( strMemberName, strEmail, 'Auto Send');
end if;
DBMS_OUTPUT.PUT_LINE('finished');
end loop;
End PROCSEND_KOEMAIL;
****************************************
