Kirim email pake plsql
Contoh
function send_html_mail
( p_from VARCHAR2, -- dapat menerima email dari alamat ybs: "Nama
Anda" <[EMAIL PROTECTED]>;
p_to varchar2, -- untuk kirim ke beberapa email pisahkan dengan
tanda koma
p_cc varchar2,
p_bcc varchar2,
p_subject VARCHAR2,
p_body VARCHAR2, -- body bisa berupa html tags.
p_smtp_server varchar2 default 'jkt-noexm2.sampoerna.co.id'
) return varchar2;
function get_chunk
(
p_big_chunk in out varchar2,
p_delimiter varchar2
) return varchar2;
end exm_mail_package;
/
CREATE OR REPLACE PACKAGE BODY exm_mail_package as
procedure send_header
(
p_conn in out utl_smtp.connection,
name in varchar2,
header in varchar2
) as
begin
utl_smtp.write_data(p_conn, name || ': ' || header || utl_tcp.crlf);
end;
function get_mail_address(p_name_mail_combo varchar2) return varchar2
is
v_ctr number;
v_result varchar2(100);
begin
v_ctr := instr(p_name_mail_combo, '<');
if v_ctr > 0 then
v_result := substr(p_name_mail_combo, v_ctr + 1,
instr(p_name_mail_combo, '>') - v_ctr - 1);
else
v_result := p_name_mail_combo;
end if;
return v_result;
end;
function send_html_mail
(
p_from VARCHAR2, -- dapat menerima email dari alamat ybs: "Nama
Anda" <[EMAIL PROTECTED]>;
p_to varchar2, -- untuk kirim ke beberapa email pisahkan dengan
tanda koma
p_cc varchar2,
p_bcc varchar2,
p_subject VARCHAR2,
p_body VARCHAR2, -- body bisa berupa html tags.
p_smtp_server varchar2 default 'jkt-noexm2.sampoerna.co.id'
) return varchar2 IS
v_conn utl_smtp.connection;
html_head VARCHAR2(255);
v_from varchar2(4000);
v_rcpt_list varchar2(4000);
v_rcpt varchar2(4000);
v_ctr number;
BEGIN
v_from := get_mail_address(p_from);
v_rcpt_list := p_to || ',' || p_cc || ',' || p_bcc;
v_ctr := 0;
while length(v_rcpt_list) <> v_ctr loop
v_ctr := length(v_rcpt_list);
v_rcpt_list := replace(v_rcpt_list, ',,', ',');
end loop;
v_conn := utl_smtp.open_connection(p_smtp_server);
while v_rcpt_list is not null loop
v_rcpt := get_chunk(v_rcpt_list, ',');
--<--Change this line
html_head := '<!doctype html public "-//w3c//dtd html 4.0
transitional//en">';
utl_smtp.helo(v_conn, p_smtp_server);
--<--Change this line
utl_smtp.mail(v_conn, v_from);
utl_smtp.rcpt(v_conn, get_mail_address(v_rcpt));
utl_smtp.open_data(v_conn);
send_header(v_conn, 'Content-Transfer-Encoding:','8bit');
send_header(v_conn, 'Content-Type', 'text/html; CHARSET=UTF-8');
send_header(v_conn, 'From', p_from);
send_header(v_conn, 'To', p_to);
send_header(v_conn, 'Cc', p_cc);
send_header(v_conn, 'Bcc', p_bcc);
send_header(v_conn, 'Subject', p_subject);
utl_smtp.write_data(v_conn, utl_tcp.CRLF || html_head || p_body);
utl_smtp.close_data(v_conn);
end loop;
utl_smtp.quit(v_conn);
return '';
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(v_conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
NULL;
-- kalo SMTP server down maka koneksi ke server tidak tersedia
END;
return sqlerrm;
when others then return sqlerrm;
END send_html_mail;
function get_chunk
(
p_big_chunk in out varchar2,
p_delimiter varchar2
) return varchar2
is
v_pos number;
v_result varchar2(255);
begin
v_pos := instr(p_big_chunk, p_delimiter);
if v_pos = 0 then
v_pos := length(p_big_chunk) + 1;
end if;
v_result := substr(p_big_chunk, 1, v_pos - 1);
p_big_chunk := substr(p_big_chunk, v_pos + 1);
return v_result;
end;
end exm_mail_package;
/
------------------------------------------------------------------------
------------------
Contoh Kirim email pake webutil/komponen java
Di form di panggil pada event when_buttone_pressed
PROCEDURE jebrod IS
hAlert ALERT := FIND_ALERT('MAIL_QUESTION');
iAns PLS_INTEGER;
len integer;
lastchar varchar2(1);
jex ORA_JAVA.JEXCEPTION;
RetVal varchar2(200);
P_FROM VARCHAR2(200);
P_TO VARCHAR2(200);
P_CC VARCHAR2(200);
P_BCC VARCHAR2(200);
P_SUBJECT VARCHAR2(200);
P_BODY VARCHAR2(2000);
P_SMTP_SERVER VARCHAR2(200);
a varchar2(200);
b varchar2(200);
c varchar2(200);
x number:=0;
BEGIN
-- ini untuk external
-- P_FROM := 'Administrator';
-- P_TO := '"Ade Hendi"@sampoerna,[EMAIL PROTECTED]';
for i in (select email,user_name from EXM_USER_MST where user_id in
(
select user_id from
FND_USER_RESPONSIBILITIES
where responsibility_id
in(
select responsibility_id from FND_RESPONSIBILITIES
where fieldname1='DOC' ))and email!='-')loop
x:=x+1;
a:=i.email;
b:=b||','||a;
ProgressBar.setBackground('white');
ProgressBar.setForeground('orange');
ProgressBar.setPercentage(x);
synchronize;
End Loop;
c:=substr(b,2,length(b));
msgbox('mail sent to '||c,'Exim Mail');
:global.email_ceurik:=c;
P_FROM := 'EXIM-IS';
P_BCC := NULL;
P_SUBJECT := 'Import Request '||:EXM_HDR_IRF.RFI_NO;
P_BODY := 'Need Your Action to create Import Order for
'||chr(13)||chr(13)||
'RFI No
: '||:EXM_HDR_IRF.RFI_NO||chr(13)||
'Transaction
Type : Import Request '||chr(13)||
'Requestor
: '||:GLOBAL.USER_NAME||chr(13)||
'Email
Address : '||:global.email||chr(13)||chr(13)||chr(13)||
'Best
regards,'||chr(13)||:GLOBAL.USER_NAME||chr(13)||
:global.email;
SendMessage.send(get_smtp_server,
'EXIM-IS',
c,
P_SUBJECT,
P_BODY,
:EXM_HDR_IRF.ATTACHMENT);
MsgBoxjava.Show(MsgBoxjava.NOTE_ALERT,'Message Sent');
EXCEPTION
WHEN ORA_JAVA.EXCEPTION_THROWN then
begin
jex := ORA_JAVA.LAST_EXCEPTION;
MsgBoxjava.Show(MsgBoxjava.STOP_ALERT,SendMessageException.toString(jex)
);
exception
WHEN ORA_JAVA.EXCEPTION_THROWN then
MsgBoxjava.Show(MsgBoxjava.STOP_ALERT,'Your classpath is
not correctly defined in your environment file. The JavaMail jar files
cannot be located');
end;
WHEN ORA_JAVA.JAVA_ERROR then
MsgBoxjava.Show(MsgBoxjava.STOP_ALERT,'Java Error:
'||ORA_JAVA.LAST_ERROR);
END;
_____
From: [email protected] [mailto:[EMAIL PROTECTED]
On Behalf Of fata hudaya
Sent: Monday, January 29, 2007 12:03 PM
To: [email protected]; [EMAIL PROTECTED]
Subject: [indo-oracle] Kirim Email Dengan Attachment di PL/SQL
Dear Masters & Friends,
Kalo saya mo kirim email + attachments (berupa text file) dari procedure
PL/SQL gimana ya ?
Soalnya saya belum pernah buat procedure spt ini. Semoga teman2 punya
pengalaman dengan masalah ini, mohon dishare...:D, Please help me...
Many Thanks & Best Regards,
Fata Hudaya,
+628567143271
[EMAIL PROTECTED] <mailto:elfhatoy%40gmail.com>
http://elhoedaya.bravehost.com <http://elhoedaya.bravehost.com>
__________________________________________________________
Inbox full of unwanted email? Get leading protection and 1GB storage
with All New Yahoo! Mail. http://uk.docs.yahoo.com/nowyoucan.html
<http://uk.docs.yahoo.com/nowyoucan.html>
[Non-text portions of this message have been removed]
The information contained in this email is or may be confidential, legally
privileged, and proprietary in nature or otherwise protected by law from
disclosure and is intended solely for the use of the addressee. If you are not
the intended recipient, you are hereby notified that any disclosure,
dissemination, distribution, copying or use of any part of this mail is
strictly prohibited and unlawful. If you received this email in error, please
immediately notify the sender or our email administrator at [EMAIL PROTECTED]
and delete it from your system. Thank you.
[Non-text portions of this message have been removed]
