Hello All,
I'm using the following code to e-mail the reults of a query. I'm not sure if this is the best way to go about it. I'm using Oracle SQL Developer 3.2. I found this code on an Oracle forum but could use some help getting it to work. I have editted it to hopefully meet my needs but it is not working. I'm simply trying to e-mail the results of the select statement. Any help would be greatly appreciated.
declare
myRecipients varchar2(100) := 'ed.brooks@mesd.us';
mySubject varchar2(100) := 'Please Assign Student E-mails';
myColumns varchar2(200) :=
'LastFirst'
||chr(9)||
'StudentNumber'
||chr(9)||
'SchoolID'
||chr(9)||
'FourthColumn'
||chr(9)||
BEGIN
SELECTÂ a.DCID, a.LASTFIRST, b.EMAIL_STUDENT, a.STUDENT_NUMBER, a.SCHOOLID
BULK COLLECT INTO StudentsWithoutEmail
from students a
INNER JOIN U_STUDENTSUSERFIELDS b ON a.DCID = b.STUDENTSDCID
WHERE a.ENTRYDATE >= '12-AUG-19'
AND a.EXITDATE <= '22-MAY-20'
AND b.EMAIL_STUDENT IS NULL
IF StudentsWithoutEmail.count > 0 THEN
xmessage := 'Please eneter an e-mail for the following students.'
||chr(10)||chr(13);
myColumns
||chr(10)||chr(13);
FOR i IN StudentsWithoutEmail.FIRST..StudentsWithoutEmail.LAST
LOOP
xmessage := xmessage ||
StudentsWithoutEmail(i).LastFirst
||chr(9)||
StudentsWithoutEmail(i).StudentNumber
||chr(9)||
StudentsWithoutEmail(i).SchoolID
||chr(10)||chr(13);
END LOOP;
ELSE
xmessage :=
'All students currently have an e-mail.'
||chr(10)||chr(13)||
END IF;
sys.utl_mail.send( 'ed.brooks@mesd.us', myRecipients,null, null, mySubject, xmessage, 'text/plain; charset=us-ascii', 3);
END;