https://docs.oracle.com/en/cloud/paas/integration-cloud/user-processes/using-processes-oracle-integration.pdf
Wednesday, July 20, 2022
How to send test mail from workflow mailer. Send oracle workflow notifications to an email address
Notification mailer is uses to send Email Notifications to the User. In Notification mailer but when we are doing some testing in Notification Mailer and we don't want to send the Test email to the Business users then we can set one Test Email address through which we can route all the test emails from , Workflow Notification mailer to that Test Email Address.
STEP 1
nav => Workflow Administratorthen oracle Application Manager
make sure the Notification Mailier Up (⯟)
then edit
send any message
.png)
API to view the email sent by workflow notification in Oracle apps R12 (WF_MAIL.GETMESSAGE)
API to view the email sent by workflow notification in Oracle apps R12 (WF_MAIL.GETMESSAGE)
SET SERVEROUPUT ON;
DECLARE ln_nid NUMBER; lv_node VARCHAR2(100); lv_agent VARCHAR2(100); lv_replyto VARCHAR2(100); lv_subject VARCHAR2(2000); lv_text_body VARCHAR2(32000); lv_html_body VARCHAR2(32000); lv_body_atth VARCHAR2(32000); lv_error_result VARCHAR2(100); z NUMBER; ln_body_length NUMBER; -- Notification id for which we need the email message content ln_notification_id NUMBER DEFAULT 123073; PROCEDURE display_big_value( piv_message VARCHAR2) IS BEGIN z := 1; ln_body_length := 0; ln_body_length := LENGTH(piv_message); IF ln_body_length > 0 THEN LOOP IF ln_body_length < z + 255 THEN dbms_output.put_line(SUBSTR(piv_message, z, ln_body_length - z + 1)); ELSE dbms_output.put_line(SUBSTR(piv_message, z, 255)); END IF; EXIT WHEN z > ln_body_length; z := z + 255; END LOOP; END IF; END; BEGIN -- Temporarily set notification status as MAIL UPDATE wf_notifications SET status = 'OPEN', mail_status = 'MAIL' WHERE notification_id = ln_notification_id; dbms_output.enable(100000); wf_mail.getmessage(ln_notification_id, 'node', NULL, 'replyto@node.com', lv_subject, lv_text_body, lv_html_body, lv_body_atth, lv_error_result); dbms_output.put_line('**************** SUBJECT IS ******************'); display_big_value(lv_subject); dbms_output.put_line('**************** TEXT BODY IS *****************'); display_big_value(lv_text_body); dbms_output.put_line('**************** HTML BODY IS *******************'); display_big_value(lv_html_body); dbms_output.put_line('**************** HTML ATTH IS ********************'); display_big_value(lv_body_atth); ROLLBACK; -- This is to rollback the temporary assignment. END;Query to find Requisitions Approval Pending
Select Q.org,Q.Requestor_Name, Q.Status, Count (*) No_Of_Lines
From (Select Prla.Destination_Organization_Id,(SELECT ORGANIZATION_CODE FROM apps.org_organization_definitions
where ORGANIZATION_ID=Prla.Destination_Organization_Id)Org,
(Select Last_Name
From Apps.Per_All_People_F Papf
Where Papf.Person_Id = Prla.To_Person_Id
And Sysdate Between Effective_Start_Date
And Effective_End_Date) Requestor_Name,
Prha.Authorization_Status Status
From Apps.Po_Requisition_Headers_All Prha,
Apps.Po_Requisition_Lines_All Prla
Where Prha.Requisition_Header_Id = Prla.Requisition_Header_Id
And Prha.Creation_Date < Sysdate - 3
And Prha.Authorization_Status Not In ('APPROVED', 'CANCELLED')
And Nvl (Prla.Closed_Code, 'OPEN') <> 'FINALLY CLOSED'
And Prla.Destination_Organization_Id = :P_Organization_Id
And Prha.Org_Id = 687) Q
Group By Q.org,Q.Requestor_Name, Q.Status
Order By Q.org,Q.Requestor_Name, Q.Status
How To Send Notification To Multiple Users - ADHOC
How To Send Notification To Multiple Users
Oracle Workflow roles are stored in the database, in the Oracle Workflow directory service.The performer can be an item type attribute that dynamically returns a role.To send a single notification(FYI/Actionable) to multiple users we have to use Role attribute as performer of that notification.The attribute value must be the internal name of a role. Here we will discuss how can we send a single notification to multiple users. Basic Requirement Our old requirement looks like "when a person applies for a leave it should go his/her supervisor for approval.Approver must be able to provide his/her approval/rejection comments. The comments must be entered by the approver while rejecting the leave requisition.If he/she rejects the leave requisition without entering comments, then it should raise an application error.If he/she approves the requisition it should be optional.Approver must also be able to select theOrganization Leave Type from a list of values . If the leave gets rejected, don’t store any information in database.The approver should able to view/edit the person "Special Information Types" before approving the leave request.Before approving the sick leave request the approver must able to see the submitted medical document." Now we will twist our original requirement- Instead of going to Supervisor for approval, all the leave request must go to the following identified people
Solution Approach First we will discuss about the changes that we need to do in workflow Definition. A) Workflow Part 1) Load the latest workflow definition from database. 2) Create an item attribute of Type Role Internal Name:- XX_TEST_ROLE Display Name:- Test Role for Approval Type :- Role   4) Validate your design and save it to database. B) Role Definition create Part Role can be defined and used in worklfow in two different ways. a) Create a Global Application Role b) Create database Adhoc Role We will first discuss how can we define Role from Oracle Application and use the same in workflow. a) Create a Global Application Role and its usages in workflow i) Go to UK HRMS Manage >> Transaction Maintanance >> Global Roles ii) Create your custom Global Role and add the desired users (Users must have a valid active fnd_user entry). Save your definition.  iii) Our Application Role definition creation is complete now we have to set the value for our newly created Role attribute. The value of the Role attribute should be the name of the role. Workflow engine will take the names from the role and will send the notification automatically. We will set the value for Role attribute in our Trigger workflow procedure (Where we are setting the other attribute values) To set the Role attribute we need to call wf_engine.SetItemAttrText procedure. This will internally check for Role attribute(though this utility is for setting Text attribute) and perform the task accordingly.  iv) Now compile the package and workflow is ready to trigger. b) Create database Adhoc Role and usages Instead of creating a Global Role from application we can also create an database adhoc role and use the same to send notification. i) Create a adhoc Role using wf_directory.CreateAdHocRole ultility. This will create a role in database,. wf_directory.CreateAdHocRole(role_name in out varchar2, role_display_name in out varchar2 language in varchar2 default null, Enter the value for "role_name" (Must not be more than 320 character and keep the name in uppercase) and "role_display_name" parameter. Keep the other parameter to its default value. If you want to make this role inactive after definite time period enter value for it (Ex:- for 365 days it should be sysdate+365. Means after 365 days role will be expired). Here role_name =>XX_TEST_ADHOC_ROLE role_display_name=>Test Adhoc Role ii) Add the user to the newly created Role using wf_directory.AddUsersToAdHocRole utility. wf_directory.AddUsersToAdHocRole(role_name in varchar2, c in varchar2 ) Here role_name => Internal name of the role role_name => User name of the list of users that we need to add. The user name of the users must be either comma/space separated. iii) Now set the Role attribute value. The value of the Role attribute should be the Internal name of the adhoc role. To set the Role attribute we need to call wf_engine.SetItemAttrText procedure. This will internally check for Role attribute(though this utility is for setting Text attribute) and perform the task accordingly.  iv) Now compile procedure and workflow is ready to trigger. Note:- 1) As we see we can define Role in two ways 1) Global Application Role 2) Database Adhoc Role. When we have a identified static list of approver/stakeholder to whom we need to send the notification we will use the Global Application Role. It is easy to maintain. When we have a dynamic list of approver/stakeholder (if our requirement is something like send the notification to all the people who are in a particular grade) to whom we need to send the notification we will use the database adhoc Role. 2) If we check the "Expand Roles"(Double click on notification >> Notification Tab) check box it will send an individual copy of the notification message to each user in the role(The notification id will be different). The notification remains in a user’s notification queue until the user responds or closes the notification. Since this is a actionable notification and we are not using voting activity thus "First responder Win" happen. If any of the role performer acts on the notification it will "Closed" in his/her queue. Whereas other will see it as "Cancelled".  3) Oracle Workflow does not support including the action history in a notification with the Expand Roles check box selected, which causes a separate copy of the notification to be sent to each user in the recipient role.  |
Email From Oracle PL/SQL (UTL_SMTP)
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_message IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
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.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;
/The code below shows how the procedure is called.
BEGIN
send_mail(p_to => 'me@mycompany.com',
p_from => 'admin@mycompany.com',
p_message => 'This is a test message.',
p_smtp_host => 'smtp.mycompany.com');
END;
/Multi-Line Emails
Multi-line messages can be written by expanding the UTL_SMTP.DATA command using the UTL_SMTP.WRITE_DATA command as follows. This is a better method to use as the total message size is no longer constrained by the 32K limit on a VARCHAR2 variable. In the following example the header information has been included in the message also.
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
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_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
/The code below shows how the procedure is called.
BEGIN
send_mail(p_to => 'me@mycompany.com',
p_from => 'admin@mycompany.com',
p_subject => 'Test Message',
p_message => 'This is a test message.',
p_smtp_host => 'smtp.mycompany.com');
END;
/HTML Emails
The following procedure builds on the previous version, allowing it include plain text and/or HTML versions of the email. The format of the message is explained here.
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
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_text_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/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
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;
/The code below shows how the procedure is called.
DECLARE
l_html VARCHAR2(32767);
BEGIN
l_html := '<html>
<head>
<title>Test HTML message</title>
</head>
<body>
<p>This is a <b>HTML</b> <i>version</i> of the test message.</p>
<p><img src="http://oracle-base.com/images/site_logo.gif" alt="Site Logo" />
</body>
</html>';
send_mail(p_to => 'me@mycompany.com',
p_from => 'admin@mycompany.com',
p_subject => 'Test Message',
p_text_msg => 'This is a test message.',
p_html_msg => l_html,
p_smtp_host => 'smtp.mycompany.com');
END;
/Emails with Attachments
Sending an email with an attachment is similar to the previous example as the message and the attachment must be separated by a boundary and identified by a name and mime type.
BLOB Attachment
Attaching a BLOB requires the binary data to be encoded and converted to text so it can be sent using SMTP.
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_blob IN BLOB 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#*=';
l_step PLS_INTEGER := 57;
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/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_text_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/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
IF p_attach_name 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: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))) || UTL_TCP.crlf);
END LOOP;
UTL_SMTP.write_data(l_mail_conn, 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;
/Thanks to the comments for pointing out the problem with the step size and the need for the extra CRLFs in the loop to prevent a long attachment breaking RFC 2045.
The code below shows how the procedure is called.
DECLARE
l_name images.name%TYPE := 'site_logo.gif';
l_blob images.image%TYPE;
BEGIN
SELECT image
INTO l_blob
FROM images
WHERE name = l_name;
send_mail(p_to => 'me@mycompany.com',
p_from => 'admin@mycompany.com',
p_subject => 'Test Message',
p_text_msg => 'This is a test message.',
p_attach_name => 'site_logo.gif',
p_attach_mime => 'image/gif',
p_attach_blob => l_blob,
p_smtp_host => 'smtp.mycompany.com');
END;
/CLOB Attachment
Attaching a CLOB is similar to attaching a BLOB, but we don't have to worry about encoding the data because it is already plain text.
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_clob IN CLOB 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#*=';
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
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/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_text_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/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
IF p_attach_name 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: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_clob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step, i * l_step + 1));
END LOOP;
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;
/The code below shows how the procedure is called.
DECLARE
l_clob CLOB := 'This is a very small CLOB!';
BEGIN
send_mail(p_to => 'me@mycompany.com',
p_from => 'admin@mycompany.com',
p_subject => 'Test Message',
p_text_msg => 'This is a test message.',
p_attach_name => 'test.txt',
p_attach_mime => 'text/plain',
p_attach_clob => l_clob,
p_smtp_host => 'smtp.mycompany.com');
END;
/Multiple Recipients
When dealing with multiple recipients, the UTL_SMTP.RCPT procedure needs to be called for each recipient, whether they are a "TO", "CC" or "BCC". The destinction between the types of recipient is made in the descriptions in the WRITE_DATA calls. The following procedure accepts comma separated "TO", "CC" and "BCC" parameters. The "TO" is mandatory, but the others are optional. If present, they are processed appropriately by splitting the strings up using the string_api package.
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_cc IN VARCHAR2 DEFAULT NULL,
p_bcc IN VARCHAR2 DEFAULT NULL,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
PROCEDURE process_recipients(p_mail_conn IN OUT UTL_SMTP.connection,
p_list IN VARCHAR2)
AS
l_tab string_api.t_split_array;
BEGIN
IF TRIM(p_list) IS NOT NULL THEN
l_tab := string_api.split_text(p_list);
FOR i IN 1 .. l_tab.COUNT LOOP
UTL_SMTP.rcpt(p_mail_conn, TRIM(l_tab(i)));
END LOOP;
END IF;
END;
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);
process_recipients(l_mail_conn, p_to);
process_recipients(l_mail_conn, p_cc);
process_recipients(l_mail_conn, p_bcc);
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);
IF TRIM(p_cc) IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, 'CC: ' || REPLACE(p_cc, ',', ';') || UTL_TCP.crlf);
END IF;
IF TRIM(p_bcc) IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, 'BCC: ' || REPLACE(p_bcc, ',', ';') || UTL_TCP.crlf);
END IF;
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_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
/Miscellaneous
The UTL_SMTP package requires Jserver which can be installed by running the following scripts as SYS.
SQL> @$ORACLE_HOME/javavm/install/initjvm.sql SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql
Subscribe to:
Posts (Atom)
-
You are not setup as a worker. To access this Page you need to be a worker. EBS R12 this is the problem How to solve it... -
case we needs to Download The HR Workflow but instead of English Descriptions and all the Subjects we needs to appears it by Arabic languag...
