Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Email (Oracle 11gr2)
 PL/SQL Email [message #657518] |
Sat, 12 November 2016 05:20  |
 |
VariableReset
Messages: 3
Registered: November 2016
|
Junior Member |
|
|
Hello Experts:
I have a pretty simple email procedure that is SUPPOSED to send one email per supervisor listing all of their employees. However, the first email goes out correctly, but the subsequent emails include other employees in their email. The procedure correctly builds the html table for each employee group by supervisor, but incorrectly places other supervisor's employees in their email. In other words, I have the following data:
CREATE TABLE "TEMP_PER"
( "EMP_TYPE" VARCHAR2(3),
"LAST_NAME" VARCHAR2(55),
"FIRST_NAME" VARCHAR2(55),
"SUP_EMAIL" VARCHAR2(125)
) ;
REM INSERTING into TEMP_PER
SET DEFINE OFF;
Insert into TEMP_PER (EMP_TYPE,LAST_NAME,FIRST_NAME,SUP_EMAIL) values ('A','Smith','Employee','Supervisor1@mycompany.com');
Insert into TEMP_PER (EMP_TYPE,LAST_NAME,FIRST_NAME,SUP_EMAIL) values ('A','Rogers','Employee','Supervisor1@mycompany.com');
Insert into TEMP_PER (EMP_TYPE,LAST_NAME,FIRST_NAME,SUP_EMAIL) values ('C','Velasquez','Employee','Supervisor1@mycompany.com');
Insert into TEMP_PER (EMP_TYPE,LAST_NAME,FIRST_NAME,SUP_EMAIL) values ('B','Robbins','Employee','Supervisor2@mycompany.com');
INSERT INTO TEMP_PER (EMP_TYPE,LAST_NAME,FIRST_NAME,SUP_EMAIL) VALUES ('B','Jones','Employee','Supervisor2@mycompany.com');
Supervisor1@mycompany.com should receive the following email with the procedure below:
Last Name
Smith
Rogers
Velasquez
Supervisor2@mycompany.com should receive the following email with the procedure below:
Last Name
Robbins
Jones
Instead, Supervisor1@mycompany.com receives the correct table as above, but Supervisor2@mycompany.com recieves BOTH tables in the body of the email like:
Last Name
Smith
Rogers
Velasquez
Last Name
Robbins
Jones
This is my current procedure:
CREATE OR REPLACE PROCEDURE PER_RECON
AS
l_body_text CLOB;
l_body_html CLOB;
-- Fetch all supervisor email addresses
CURSOR c1 IS
SELECT UNIQUE sup_email FROM temp_per WHERE SUP_EMAIL IS NOT NULL;
-- Fetch relevant employees for each supervisor
CURSOR C2 (P_SUPERVISOR temp_per.sup_email%TYPE) IS
SELECT LAST_NAME, SUP_EMAIL FROM TEMP_PER WHERE SUP_EMAIL = P_SUPERVISOR;
BEGIN
l_body_html := NULL;
-- Begin HTML script and style sheet
l_body_html := l_body_html ||
'
<html>
<head>
</head>
<body>
';
l_body_text := 'Plain text version';
l_body_html := l_body_html
|| '<span class="message">Good Day: <br><br>';
l_body_html := l_body_html
|| 'Enter Text Here<br><br>';
-- Insert table with relevant employee data
FOR C1_REC IN C1
LOOP
l_body_html := l_body_html
|| '<table cellpadding="8";style="border-collapse: collapse; border: 1px solid black"><tbody>'||'<tr>'
|| '<th>Last Name</th>'
|| '</tr>' ;
l_body_html := l_body_html
||'</span>';
FOR C2_REC IN C2 (C1_REC.SUP_EMAIL)
LOOP
l_body_html := l_body_html
||'<tr>'
|| '<td style="border: 1px solid black; font-size: 11px;FONT-WEIGHT:BOLD;font-family: calibri,Trebuchet MS, Verdana; ">'
|| C2_REC.last_name
||'</td>';
l_body_html := l_body_html
||'</tr>';
END LOOP;
l_body_html := l_body_html
||'</tbody></table><br>';
-- End of table HTML
WWV_FLOW_API.SET_SECURITY_GROUP_ID(xxxxxxxxxxxxxxxxx);
APEX_MAIL.SEND (
p_to => C1_REC.SUP_EMAIL,
P_FROM => 'No-Reply@xxxxx.com,
p_body => l_body_text,
p_body_html => l_body_html,
P_SUBJ => '**Subject**');
-- End HTML script and HTML body
l_body_html := l_body_html ||'</body></html>';
END LOOP;
BEGIN
apex_mail.push_queue (p_smtp_hostname => 'xx.xx.xx.xx',
p_smtp_portno => 'xx');
END;
END PER_RECON; |
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 05:45:16 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
