The code here seems to be ok. I'm getting this exception :( please correct me. Getting exception PLS-00103 and seems to be correct
The code here seems to be ok. I'm getting this exception :( please correct me. Getting exception PLS-00103 and seems to be correct
DECLARE
l_body varchar2(5000);
l_body_html varchar2(5000);
l_workspace_id number;
DiscontinuationEmail varchar2(5000);
DiscontinuationSubject varchar2(5000);
Discontinuation varchar2(5000);
DiscontinuationCC varchar2(5000);
DiscontinuationReminder number;
cursor cDiscontinuation is select NETSEC_TEAM.ENGINEER_EMAIL as ENGINEER_EMAIL,
NETSEC_TEAM.NETSEC_MGR as NETSEC_MGR
from NETSEC_TEAM NETSEC_TEAM
Where NETSEC_TEAM.NETSEC_MGR <> 'ALL'
MINUS
select NETSEC_PROCOMPLIANCE_1.EMPLOYEE_EMAIL as EMPLOYEE_EMAIL,
NETSEC_PROCOMPLIANCE_1.MANAGER_EMAIL as MANAGER_EMAIL
from NETSEC_PROCOMPLIANCE_1 NETSEC_PROCOMPLIANCE_1
Where NETSEC_PROCOMPLIANCE_1.DOCUMENT_NAME ='Discontinuation of Daily BI Agent Reports';
-- ****
DOD cDiscontinuation%rowtype;
BEGIN
l_workspace_id := apex_util.find_security_group_id (p_workspace => 'CIT-CSCOE-PROD');
apex_util.set_security_group_id (p_security_group_id => l_workspace_id);
/* *********** Discontinuation ********************** */
OPEN cDiscontinuation
Discontinuation :='';
DiscontinuationCC :='';
DiscontinuationReminder := 1;
SELECT REMINDER INTO DiscontinuationReminder
FROM NETSEC_COMPLIANCE_REMINDER WHERE COMPLIANT ='Discontinuation';
Loop
FETCH cDiscontinuation INTO DOD;
EXIT WHEN cDiscontinuation%NOTFOUND;
Discontinuation := Discontinuation || ','|| DOD.ENGINEER_EMAIL;
DiscontinuationCC := DiscontinuationCC || ','|| DOD.NETSEC_MGR;
END LOOP;
UPDATE NETSEC_COMPLIANCE_REMINDER SET REMINDER = DiscontinuationReminder+1
WHERE COMPLIANT ='Discontinuation';
DiscontinuationReminder := DiscontinuationReminder + 1;
CLOSE cDiscontinuation;
DiscontinuationEmail:='TO: '|| Discontinuation ||' <br><br> CC: ' || DiscontinuationCC ||'
<p>If you are a "To:" recipient of this email, it means that you are not in compliance with the following Network Services team announcement:<br />
If you are a "Cc:" recipient of this email, it means that one or more of your employees are not in compliance with the following Network Services team announcement:</p>
<p><a href="https://apex.oraclecorp.com/pls/apex/f?p=1648:522">Discontinuation of Daily BI Agent Reports</a></p>
<p>Please click on the link above and acknowledge and confirm your understanding of the process.</p>
<p>Thank you.</p>';
Discontinuation:= '***** NON-COMPLIANT ***** Discontinuation of Daily BI Agent Reports- RELEASE DATE 25 MAY 2016';
apex_mail.send(
p_to => 'bernardo.troncoso@oracle.com',
p_from => 'bernardo.troncoso@oracle.com',
p_cc => '',
p_body => DiscontinuationSubject,
p_body_html => DiscontinuationEmail,
p_subj => 'REMINDER #'|| DiscontinuationReminder || ' ' || DiscontinuationSubject,
p_bcc => '',
p_replyto => NULL
);
END;
You're missing a semicolon after
OPEN cDiscontinuation
Just a comment on style: is there any reason why you've separated the OPEN from the FETCH? You could also use a CURSOR FOR LOOP - just cleans the code up a little. So instead of :
OPEN cDiscontinuation
LOOP
FETCH cDiscontinuation INTO DOD;
EXIT WHEN cDiscontinuation%NOTFOUND;
Discontinuation := Discontinuation || ','|| DOD.ENGINEER_EMAIL;
DiscontinuationCC := DiscontinuationCC || ','|| DOD.NETSEC_MGR;
END LOOP;
You could code
FOR R_discontinuation IN cDiscontinuation LOOP
Discontinuation := Discontinuation || ','|| R_discontinuation.ENGINEER_EMAIL;
DiscontinuationCC := DiscontinuationCC || ','|| R_discontinuation.NETSEC_MGR;
END LOOP ;
In this case R_discontinuation is exactly equivalent to DOD in the first example (that is, cDiscontinuation%ROWTYPE) but is declared implicitly.
Using the CURSOR FOR LOOP is no more correct that your approach, just cleaner