Databases
StackOverflow2013
Postsdbo
POSQL stored procedure send emails(message when there are none results)?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL stored procedure send emails(message when there are none results)?
    primarykey
    data
    text
    <p>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) </p> <p>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. </p> <p>here is my code: </p> <pre><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 := '&lt;HTML&gt;&lt;BODY&gt;'; 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 ||'&lt;table&gt;'; v_message := v_message ||'&lt;br&gt;&lt;br&gt;'; v_message := v_message ||'&lt;b&gt;'||'EVENTS WITH ASSIGNED PERSON: TO BE DETERMINED'||'&lt;/b&gt;&lt;br&gt;'; v_message := v_message ||'&lt;table border="1"&gt;'; v_message := v_message ||'&lt;tr&gt;&lt;td width="100"&gt;'||'Lname'||'&lt;/td&gt;&lt;td width="100"&gt;'||'Fname'||'&lt;/td&gt;&lt;td width="250"&gt;'||'Event Date'||'&lt;/td&gt;&lt;td width="100"&gt;'||'Event Name'||'&lt;/td&gt;&lt;/td&gt;&lt;td width="100"&gt;'||'Meridian'||'&lt;/td&gt;&lt;/tr&gt;'; 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 &gt;= sysdate AND b.event_date &lt;= sysdate+90 ) LOOP v_message := v_message||'&lt;tr border="1"&gt;&lt;td width="100"&gt;'||cur_rec.lname||'&lt;/td&gt;&lt;td width="100"&gt;'||cur_rec.fname||'&lt;/td&gt;&lt;td width="250"&gt;'||cur_rec.event_date||'&lt;/td&gt;&lt;td width="100"&gt;'||cur_rec.event_name||'&lt;/td&gt;&lt;td width="100"&gt;'||cur_rec.meridian||'&lt;/td&gt;&lt;/tr&gt;'; END LOOP; v_message := v_message ||'&lt;/table&gt;'; v_message := v_message ||'&lt;br&gt;&lt;br&gt;'; v_message := v_message||'&lt;/BODY&gt;&lt;/HTML&gt;'; conn := per.mail.begin_mail( sender_email =&gt; '2222@uiowa.edu', sender_name =&gt; 'jia', recip_email =&gt; email, recip_name =&gt; full_name, subject =&gt; 'Moonlighting/Outreach Verification for '||TO_CHAR(p_month, 'MM/YYYY'), mime_type =&gt; per.mail.MULTIPART_MIME_TYPE); v_message := REPLACE(v_message,'!',''); per.mail.attach_text( conn =&gt; conn, data =&gt; v_message, mime_type =&gt; 'text/HTML'); per.mail.end_mail( conn =&gt; 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; </code></pre> <p>Any ideas guys? Thanks in advance!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBray
    1. USBray
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. CO1. Why do you want to send an e-mail when there are no events to display (esp. an email starting with "There are one or more event ...")? 2. In any case, can't you just use `IF SELECT COUNT * FROM [` fill in appropriately - probably it would be the SELECT just after "*cur_rec IN*" `] = 0` {send message about no events} `ELSE` {send what you're sending now} ?
      singulars
      1. POSQL stored procedure send emails(message when there are none results)?
      1. USHelena
    2. COThanks for your comment @Helena. if use what you suggested, it just gave me error when compiling. I cant add condition such as =0 in the middle of for statement and the loop...
      singulars
      1. POSQL stored procedure send emails(message when there are none results)?
      1. USBray
    3. COOh, it's true that what I've written doesn't actually work... shame on me. How about storing the count in an integer variable beforehand: `SELECT $event_count = COUNT * FROM [` fill in appropriately - probably it would be the SELECT just after "cur_rec IN" `]` (having previously declared `$event_count` as an integer) and then testing whether it's zero or positive? I.e. `IF $event_count = 0 THEN ... ELSE ...`. I believe an IF statement itself shouldn't be causing an error whether it's inside a FOR loop or not (except when there's a syntactic error).
      singulars
      1. POSQL stored procedure send emails(message when there are none results)?
      1. USHelena
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload