Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
braybray
Starting Member
1 Post |
 Posted - 2013-08-22 : 17:05:29
|
| I am writing a procedure in oracle sql that sends email to clients reminding them of certain important information. Right now I have the correct procedure and the email can be sent successfully. I am just wondering how I can create a message instead of an empty result table if I have nothing to display say there are none events assgined with person " to be determined".(but i want to keep the loop still so when there is data it will display) I tried the if statment and count the result but it keeps giving me errors when compile the code. I cant have any condition such as if cnt=0 then....in the middle of for statement and the loop. here is my code: CREATE OR REPLACE PROCEDURE DOM.TEST_SCHED_EMAIL( p_month DATE) IS conn utl_smtp.connection; v_message VARCHAR2(12000); v_log VARCHAR2(500); email_id NUMBER; rec_person_id NUMBER; email VARCHAR2(100); full_name VARCHAR2(100); CURSOR recipients_cursor IS SELECT DISTINCT p.person_id, email_address, p.fname ||' ' ||p.lname AS full_name FROM per.person p WHERE person_id =67786895; BEGIN -- dbms_output.enable (50000); OPEN recipients_cursor; LOOP FETCH recipients_cursor INTO rec_person_id, email, full_name; EXIT WHEN recipients_cursor%NOTFOUND; BEGIN v_message := '<HTML><BODY>'; v_message := v_message ||'There are one or more event with person assigned as To Be Determined. These events are under 90 days of its scheduled date. Please take appropriate action for the events listed below'||'.'; v_message := v_message ||'<table>'; v_message := v_message ||'<br><br>'; v_message := v_message ||'<b>'||'EVENTS WITH ASSIGNED PERSON: TO BE DETERMINED'||'</b><br>'; v_message := v_message ||'<table border="1">'; v_message := v_message ||'<tr><td width="100">'||'Lname'||'</td><td width="100">'||'Fname'||'</td><td width="250">'||'Event Date'||'</td><td width="100">'||'Event Name'||'</td></td><td width="100">'||'Meridian'||'</td></tr>'; FOR cur_rec IN (SELECT a.fname, a.lname, b.event_date, b.event_name, b.meridian FROM cal.appt_person_event_statustype b JOIN per.person a ON a.person_id=b.person_id WHERE upper(a.lname) LIKE '%TO BE%' AND b.event_date >= sysdate AND b.event_date <= sysdate+90 ) LOOP v_message := v_message||'<tr border="1"><td width="100">'||cur_rec.lname||'</td><td width="100">'||cur_rec.fname||'</td><td width="250">'||cur_rec.event_date||'</td><td width="100">'||cur_rec.event_name||'</td><td width="100">'||cur_rec.meridian||'</td></tr>'; END LOOP; v_message := v_message ||'</table>'; v_message := v_message ||'<br><br>'; v_message := v_message||'</BODY></HTML>'; conn := per.mail.begin_mail( sender_email => '2222@uiowa.edu', sender_name => 'jia', recip_email => email, recip_name => full_name, subject => 'Moonlighting/Outreach Verification for '||TO_CHAR(p_month, 'MM/YYYY'), mime_type => per.mail.MULTIPART_MIME_TYPE); v_message := REPLACE(v_message,'!',''); per.mail.attach_text( conn => conn, data => v_message, mime_type => 'text/HTML'); per.mail.end_mail( conn => conn ); EXCEPTION WHEN OTHERS THEN per.mail.mail('2222@uiowa.edu', 'Moonlighting Notify', '2222@uiowa.edu', 'IntMed-OracleHelp', 'Moonlighting Notification Error', 'rec_person_id='||rec_person_id||utl_tcp.CRLF ||'Email:'||email||utl_tcp.CRLF ||'Name:'||full_name||utl_tcp.CRLF ||'Error Code:'||SQLCODE); END; END LOOP; END;Any ideas guys? Thanks in advance |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-23 : 00:20:26
|
| Hi,This is SQL Server forums.. You might get quick responses by posting dbforums.com--Chandu |
 |
|
|
|
|
|
|
|
SQL stored procedure send emails(message when ther