Master OracleApps

Email Sending Program by using UTL_SMTP Mail.

Package Specification:

CREATE OR REPLACE PACKAGE xxmto_utl_smtp_email_pkg

/***************************************************************************

*----------------------- Customizable Section -----------------------------*

* Customize the signature that will appear in the email's MIME header. *

* Useful for versioning. *

***************************************************************************/

g_chr_mailer_id CONSTANT VARCHAR2 (256)

:= 'Mailer by Oracle UTL_SMTP';

/***************************************************************************

*-------------------- End Customizable Section ----------------------------*

***************************************************************************/

/***************************************************************************

* A simple email API for sending email in plain text in a single call. *

* The format of an email address is one of these: *

* someone@some-domain *

* "Someone at some domain" <someone@some-domain> *

* Someone at some domain <someone@some-domain> *

* The recipients is a list of email addresses separated by either *

* "," or ";" *

***************************************************************************/

/***************************************************************************

* Write email body in ASCII *

***************************************************************************/

PROCEDURE xxmto_write_text (

p_conn IN OUT NOCOPY UTL_SMTP.connection

,p_in_chr_message IN VARCHAR2

);

/***************************************************************************

* End the email. *

***************************************************************************/

PROCEDURE xxmto_end_mail (

p_conn IN OUT NOCOPY UTL_SMTP.connection

);

/***************************************************************************

* Extended email API to send multiple emails in a session for better *

* performance. First, begin an email session with begin_session. *

* Then, begin each email with a session by calling begin_mail_in_session *

* instead of begin_mail. End the email with end_mail_in_session instead *

* of end_mail. End the email session by end_session. *

***************************************************************************/

FUNCTION xxmto_begin_session

RETURN UTL_SMTP.connection;

/***************************************************************************

* Begin an email in a session. *

***************************************************************************/

PROCEDURE xxmto_begin_mail_in_session (

p_conn IN OUT NOCOPY UTL_SMTP.connection

,p_in_chr_sender IN VARCHAR2

,p_in_chr_recipients IN VARCHAR2

,p_in_chr_subject IN VARCHAR2

,p_in_chr_cc IN VARCHAR2 DEFAULT NULL

,p_in_chr_bcc IN VARCHAR2 DEFAULT NULL

,p_in_chr_return_receipt IN VARCHAR2 DEFAULT NULL

,p_in_chr_mime_type IN VARCHAR2 DEFAULT 'text/plain'

,p_in_int_priority IN PLS_INTEGER DEFAULT NULL

);

/***************************************************************************

* End an email in a session. *

***************************************************************************/

PROCEDURE xxmto_end_mail_in_session (

p_conn IN OUT NOCOPY UTL_SMTP.connection

);

/***************************************************************************

* End an email session. *

***************************************************************************/

PROCEDURE xxmto_end_session (

p_conn IN OUT NOCOPY UTL_SMTP.connection

);

/**************************************************************************

* Procedure to send HTML type mails *

***************************************************************************/

PROCEDURE xxmto_send_email_prc (

p_out_chr_errbuf OUT NOCOPY VARCHAR2

,p_out_num_retcode OUT NOCOPY NUMBER

,p_in_chr_sender IN VARCHAR2

,p_in_chr_recipients IN VARCHAR2

,p_in_chr_subject IN VARCHAR2

,p_in_chr_cc IN VARCHAR2 DEFAULT NULL

,p_in_chr_bcc IN VARCHAR2 DEFAULT NULL

,p_in_clb_message IN CLOB

,p_in_int_priority IN PLS_INTEGER DEFAULT NULL

,p_in_chr_return_receipt IN VARCHAR2 DEFAULT NULL

,p_in_clb_stylesheet IN CLOB DEFAULT NULL

);

/***************************************************************************

* End od Package Specification *

***************************************************************************/

END xxmto_utl_smtp_email_pkg;

show errors

=======================================================================

Package Body:

CREATE OR REPLACE PACKAGE BODY xxmto_utl_smtp_email_pkg

/***************************************************************************

* Return the next email address in the list of email addresses, separated *

* by either a "," or a ";". The format of mailbox may be in one of these: *

* someone@some-domain *

* "Someone at some domain" <someone@some-domain> *

* Someone at some domain <someone@some-domain> *

***************************************************************************/

FUNCTION xxmto_get_address (

p_in_chr_addr_list IN OUT VARCHAR2

)

RETURN VARCHAR2

l_chr_addr VARCHAR2 (256);

l_num_int PLS_INTEGER;

FUNCTION xxmto_lookup_unquoted_char (

p_in_chr_str IN VARCHAR2

,p_in_chr_chrs IN VARCHAR2

)

RETURN PLS_INTEGER

l_chr_c VARCHAR2 (5);

l_num_i PLS_INTEGER;

l_num_len PLS_INTEGER;

l_bol_inside_quote BOOLEAN;

BEGIN

l_bol_inside_quote := FALSE;

l_num_i := 1;

l_num_len := LENGTH (p_in_chr_str);

WHILE (l_num_i <= l_num_len)

LOOP

l_chr_c := SUBSTR (p_in_chr_str

,l_num_i

);

IF (l_bol_inside_quote)

THEN

IF (l_chr_c = '"')

THEN

l_bol_inside_quote := FALSE;

ELSIF (l_chr_c = '\')

THEN

l_num_i := l_num_i + 1;

-- Skip the quote character

END IF;

GOTO next_char;

END IF;

IF (l_chr_c = '"')

THEN

l_bol_inside_quote := TRUE;

GOTO next_char;

END IF;

IF (INSTR (p_in_chr_chrs, l_chr_c) >= 1)

THEN

RETURN l_num_i;

END IF;

<<next_char>>

l_num_i := l_num_i + 1;

END LOOP;

RETURN 0;

END xxmto_lookup_unquoted_char;

BEGIN

p_in_chr_addr_list := LTRIM (p_in_chr_addr_list);

l_num_int := xxmto_lookup_unquoted_char (p_in_chr_addr_list, ',;');

IF (l_num_int >= 1)

THEN

l_chr_addr := SUBSTR (p_in_chr_addr_list

, l_num_int - 1

);

p_in_chr_addr_list := SUBSTR (p_in_chr_addr_list, l_num_int + 1);

ELSE

l_chr_addr := p_in_chr_addr_list;

p_in_chr_addr_list := '';

END IF;

l_num_int := xxmto_lookup_unquoted_char (l_chr_addr, '<');

IF (l_num_int >= 1)

THEN

l_chr_addr := SUBSTR (l_chr_addr, l_num_int + 1);

l_num_int := INSTR (l_chr_addr, '>');

IF (l_num_int >= 1)

THEN

l_chr_addr :=

SUBSTR (l_chr_addr

, l_num_int - 1

);

END IF;

RETURN l_chr_addr;

END xxmto_get_address;

/***************************************************************************

* Write a MIME header *

***************************************************************************/

PROCEDURE xxmto_write_mime_header (

p_conn IN OUT NOCOPY UTL_SMTP.connection

,p_in_chr_name IN VARCHAR2

,p_in_chr_value IN VARCHAR2

)

BEGIN

UTL_SMTP.write_data (p_conn, p_in_chr_name || ': ' || p_in_chr_value || UTL_TCP.crlf);

END xxmto_write_mime_header;

/***************************************************************************

* PROCEDURE write_text *

***************************************************************************/

PROCEDURE xxmto_write_text (

p_conn IN OUT NOCOPY UTL_SMTP.connection

,p_in_chr_message IN VARCHAR2

)

BEGIN

UTL_SMTP.write_data (p_conn, p_in_chr_message);

END xxmto_write_text;

/***************************************************************************

* PROCEDURE end_mail *

***************************************************************************/

PROCEDURE xxmto_end_mail (

p_conn IN OUT NOCOPY UTL_SMTP.connection

)

BEGIN

xxmto_end_mail_in_session (p_conn);

xxmto_end_session (p_conn);

END xxmto_end_mail;

/***************************************************************************

* FUNCTION begin_session *

***************************************************************************/

FUNCTION xxmto_begin_session

RETURN UTL_SMTP.connection

l_conn UTL_SMTP.connection;

-- Customize the SMTP host, port and your domain name below.

l_chr_smtp_host VARCHAR2 (256);

l_int_smtp_port PLS_INTEGER;

l_chr_smtp_domain VARCHAR2 (256);

BEGIN

l_chr_smtp_host:= fnd_profile.VALUE ('FND_SMTP_HOST');

l_int_smtp_port := fnd_profile.value('FND_SMTP_PORT');

-- open SMTP connection

l_conn :=

UTL_SMTP.open_connection (l_chr_smtp_host, l_int_smtp_port);

--UTL_SMTP.helo (conn, smtp_domain);

UTL_SMTP.helo (l_conn, l_chr_smtp_host);

RETURN l_conn;

END xxmto_begin_session;

/***************************************************************************

* PROCEDURE begin_mail_in_session *

***************************************************************************/

PROCEDURE xxmto_begin_mail_in_session (

p_conn IN OUT NOCOPY UTL_SMTP.connection

,p_in_chr_sender IN VARCHAR2

,p_in_chr_recipients IN VARCHAR2

,p_in_chr_subject IN VARCHAR2

,p_in_chr_cc IN VARCHAR2 DEFAULT NULL

,p_in_chr_bcc IN VARCHAR2 DEFAULT NULL

,p_in_chr_return_receipt IN VARCHAR2 DEFAULT NULL

,p_in_chr_mime_type IN VARCHAR2 DEFAULT 'text/plain'

,p_in_int_priority IN PLS_INTEGER DEFAULT NULL

)

l_chr_my_recipients VARCHAR2 (32767) := p_in_chr_recipients;

l_chr_my_sender VARCHAR2 (32767) := p_in_chr_sender;

l_chr_my_cc VARCHAR2 (32767) := p_in_chr_cc;

l_chr_my_bcc VARCHAR2 (32767) := p_in_chr_bcc;

BEGIN

UTL_SMTP.mail (p_conn, xxmto_get_address (l_chr_my_sender));

-- Specify recipient(s) of the email.

WHILE (l_chr_my_recipients IS NOT NULL)

LOOP

UTL_SMTP.rcpt (p_conn, xxmto_get_address (l_chr_my_recipients));

END LOOP;

WHILE (l_chr_my_cc IS NOT NULL)

LOOP

UTL_SMTP.rcpt (p_conn, xxmto_get_address (l_chr_my_cc));

END LOOP;

WHILE (l_chr_my_bcc IS NOT NULL)

LOOP

UTL_SMTP.rcpt (p_conn, xxmto_get_address (l_chr_my_bcc));

END LOOP;

-- Start body of email

UTL_SMTP.open_data (p_conn);

-- Set "From" MIME header

xxmto_write_mime_header (p_conn

,'From'

,p_in_chr_sender

);

-- Set "To" MIME header

xxmto_write_mime_header (p_conn

,'To'

,p_in_chr_recipients

);

-- Set "Subject" MIME header

xxmto_write_mime_header (p_conn

,'Subject'

,p_in_chr_subject

);

-- Set "Content-Type" MIME header

xxmto_write_mime_header (p_conn

,'Content-Type'

,p_in_chr_mime_type

);

-- Set "X-Mailer" MIME header

xxmto_write_mime_header (p_conn

,'X-Mailer'

,g_chr_mailer_id

);

-- Set "CC" MIME Header if it is sent

IF p_in_chr_cc IS NOT NULL

THEN

xxmto_write_mime_header (p_conn

,'Cc'

,p_in_chr_cc

);

END IF;

-- Set "BCC" MIME Header if it is sent

IF p_in_chr_bcc IS NOT NULL

THEN

xxmto_write_mime_header (p_conn

,'Bcc'

,p_in_chr_bcc

);

END IF;

IF p_in_chr_return_receipt IS NOT NULL

THEN

xxmto_write_mime_header (p_conn

,'Return-Receipt-To'

,p_in_chr_return_receipt

);

END IF;

IF (p_in_int_priority IS NOT NULL)

THEN

xxmto_write_mime_header (p_conn

,'X-Priority'

,p_in_int_priority

);

END IF;

UTL_SMTP.write_data (p_conn, UTL_TCP.crlf);

IF (p_in_chr_mime_type LIKE 'multipart/mixed%')

THEN

xxmto_write_text (p_conn

, 'This is a multi-part message in MIME format.'

|| UTL_TCP.crlf);

END IF;

END xxmto_begin_mail_in_session;

/***************************************************************************

* PROCEDURE end_mail_in_session *

***************************************************************************/

PROCEDURE xxmto_end_mail_in_session (

p_conn IN OUT NOCOPY UTL_SMTP.connection

)

BEGIN

UTL_SMTP.close_data (p_conn);

END xxmto_end_mail_in_session;

/***************************************************************************

* PROCEDURE end_session *

***************************************************************************/

PROCEDURE xxmto_end_session (

p_conn IN OUT NOCOPY UTL_SMTP.connection

)

BEGIN

UTL_SMTP.quit (p_conn);

END xxmto_end_session;

/**************************************************************************

* Procedure to send HTML type mails *

***************************************************************************/

PROCEDURE xxmto_send_email_prc (

p_out_chr_errbuf OUT NOCOPY VARCHAR2

,p_out_num_retcode OUT NOCOPY NUMBER

,p_in_chr_sender IN VARCHAR2

,p_in_chr_recipients IN VARCHAR2

,p_in_chr_subject IN VARCHAR2

,p_in_chr_cc IN VARCHAR2 DEFAULT NULL

,p_in_chr_bcc IN VARCHAR2 DEFAULT NULL

,p_in_clb_message IN CLOB

,p_in_int_priority IN PLS_INTEGER DEFAULT NULL

,p_in_chr_return_receipt IN VARCHAR2 DEFAULT NULL

,p_in_clb_stylesheet IN CLOB DEFAULT NULL

)

l_conn UTL_SMTP.connection;

l_num_str_len NUMBER;

l_num_modulo NUMBER;

l_num_pieces NUMBER;

l_num_amt NUMBER := 1000;

l_num_offset NUMBER := 1;

l_chr_instance VARCHAR2(100);

l_clb_message CLOB;

l_chr_crlf VARCHAR2 (2) := CHR (13) || CHR (10);

l_chr_mime_type VARCHAR2 (100)

:= 'text/html; charset=ISO-8859-15';

l_chr_html_trailer VARCHAR2 (500) := '</body></html>';

l_clb_stylesheet CLOB

:= '

table {

border: solid 1px #444;

border-collapse: collapse;

empty-cells: show;

font-family: Calibri, Candara, Segoe, "Segoe UI", Optima, Arial, sans-serif;

font-size: 12px;

margin-left: 10%;

margin-right: 10%;

width: 80%;

}

td {

border: solid 1px #444;

}

th {

border: solid 1px #444;

}

#result_table th {

background: #4f81bd;

border-bottom: 1px solid #fff;

border-top: 1px solid #fff;

color: #fff;

font-size: 16px;

font-weight: bold;

padding: 8px;

}

#result_table td {

border-bottom: 1px solid #fff;

border-top: 1px solid transparent;

border: solid 1px #444;

color: #000;

font-size: 13px;

padding: 8px;

text-align: center;

}

#result_table .odd {

background: #e8edff;

}

#result_table .err {

border-bottom: 1px solid #fff;

border-top: 1px solid #fff;

color: #fff;

font-size: 16px;

font-weight: bold;

padding: 8px;

background: #66A3FF;

}

#message_body {

border: 0px;

border-collapse: collapse;

empty-cells: show;

font-family: Calibri, Candara, Segoe, "Segoe UI", Optima, Arial, sans-serif;

font-size: 12px;

margin-left: 10%;

margin-right: 10%;

width: 80%;

}

#message_body th {

background: #4f81bd;

border-bottom: 1px solid #fff;

border-top: 4px solid #aabcfe;

color: #fff;

font-size: 16px;

font-weight: bold;

padding: 8px;

}

#message_body tr {

border-bottom: 1px solid #fff;

border-top: 1px solid transparent;

color: #000;

font-size: 13px;

padding: 8px;

text-align: left;

}

#message_body td {

border: none;

}

#message_body pre {

border: none;

font-family: Calibri, Candara, Segoe, "Segoe UI", Optima, Arial, sans-serif;

font-size: 14px;

}

</style>

</head>

<body><div>';

BEGIN

SELECT INSTANCE_NAME

INTO l_chr_instance

FROM V$INSTANCE;

EXCEPTION

WHEN OTHERS

THEN

p_out_num_retcode := 1;

p_out_chr_errbuf := 'WARNING with Unexpected Error while fetching Instance name in Email Program - ' || SQLERRM;

END;

l_conn := xxmto_begin_session;

xxmto_begin_mail_in_session (l_conn

,p_in_chr_sender

,p_in_chr_recipients

,p_in_chr_subject

,p_in_chr_cc

,p_in_chr_bcc

,p_in_chr_return_receipt

,l_chr_mime_type

,p_in_int_priority

);

UTL_SMTP.write_raw_data (l_conn

,UTL_RAW.cast_to_raw (NVL (p_in_clb_stylesheet

,l_clb_stylesheet)));

l_clb_message := p_in_clb_message

|| '<br>'

||'** Mail sent from '

||l_chr_instance

||' instance';

l_num_str_len := DBMS_LOB.getlength (l_clb_message);

l_num_modulo := MOD (l_num_str_len, l_num_amt);

l_num_pieces := TRUNC (l_num_str_len / l_num_amt);

IF (l_num_modulo <> 0)

THEN

l_num_pieces := l_num_pieces + 1;

END IF;

FOR l_num_rec IN 1 .. l_num_pieces

LOOP

UTL_SMTP.write_raw_data

(l_conn

,UTL_RAW.cast_to_raw (DBMS_LOB.SUBSTR (l_clb_message

,1000

,l_num_offset

)));

l_num_offset := l_num_offset + 1000;

END LOOP;

UTL_SMTP.write_raw_data (l_conn

,UTL_RAW.cast_to_raw ('</div></body></html>'));

xxmto_end_mail (l_conn);

EXCEPTION

WHEN OTHERS

THEN

p_out_num_retcode := 1;

p_out_chr_errbuf := 'WARNING with Unexpected Error for Email Writing - ' || SQLERRM;

END xxmto_send_email_prc;

END xxmto_utl_smtp_email_pkg;

/***************************************************************************

* End of Package Body *

***************************************************************************/

show errors;

=======================================================================

Email Execution:

DECLARE

l_errbuf VARCHAR2(3000):=NULL;

l_retcode VARCHAR2(5):=NULL;

BEGIN

xxmto_utl_smtp_email_pkg.xxmto_send_email_prc

( p_out_chr_errbuf => l_errbuf,

p_out_num_retcode => l_retcode,

p_in_chr_sender => l_chr_from_email,

p_in_chr_recipients => l_chr_to_email,

p_in_chr_subject => 'TESTING MAIL',

p_in_chr_cc => l_chr_cc_email,

p_in_chr_bcc => l_chr_bcc_email,

p_in_clb_message => 'Hello,'

|| '<br>'

|| 'Error Message :'

|| CHR (9)

|| 'Testing mail'

|| '<br>'

|| '**This is system generated mail. Please do not reply to the mail.');

DBMS_OUTPUT.PUT_LINE(l_errbuf||l_retcode);

END;

Master OracleApps

Pages

Thursday, April 2, 2015

Email Sending Program by using UTL_SMTP Mail.

Package Specification:

Package Body:

No comments:

Post a Comment