Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Email (Oracle 11gr2)
icon9.gif  PL/SQL Email [message #657518] Sat, 12 November 2016 05:20 Go to next message
VariableReset
Messages: 2
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;
Re: PL/SQL Email [message #657519 is a reply to message #657518] Sat, 12 November 2016 06:14 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Are you setting l_body_html to NULL only once? Not for each iteration of the outer loop?
Re: PL/SQL Email [message #657520 is a reply to message #657518] Sat, 12 November 2016 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The body should be reset for each supervisor, that is move the lines from "l_body_html := NULL;" to "|| 'Enter Text Here<br><br>';" at top of C1 loop.

Re: PL/SQL Email [message #657522 is a reply to message #657520] Sat, 12 November 2016 06:48 Go to previous message
VariableReset
Messages: 2
Registered: November 2016
Junior Member
And BINGO was his name. That was it. Thanks Folks!!
Previous Topic: SQL Query Request
Next Topic: Need to match combination string at database
Goto Forum:
  


Current Time: Sat Jan 20 05:59:52 CST 2018

Total time taken to generate the page: 0.02032 seconds