Oracle PL/SQL. Pass mail address loop output to oracle email procedure

Question

I have a procedure that sends mail. I want to send a mail by selecting the email addresses from a view and pass the addresses to my mail procedure.

This code works in that it outputs multiple email addresses and incident numbers from my database.

create or replace procedure SEND_REMINDER_MAIL as

CURSOR c1 IS
SELECT contact_email
 FROM TEST.incidents_view
WHERE updated <= current_timestamp - interval '1' minute
and status_code = '100';

cursor c2 is
SELECT incident_number
FROM TEST.incidents_view WHERE updated <= current_timestamp - interval '1' minute
and status_code = '100';

v_contact_email varchar2(300);
v_incno VARCHAR2(10);

BEGIN
   -- Open the cursor and loop through the records
   OPEN c1;

      FETCH c1 INTO v_contact_email;
      EXIT WHEN c1%NOTFOUND;
      -- Print  values
      dbms_output.put_line(v_contact_email);
  end loop;
      CLOSE c1;

   open c2;
LOOP
   fetch c2 into v_incno;
   EXIT WHEN c2%NOTFOUND;
dbms_output.put_line(v_incno);
   end loop;
    CLOSE c2;
end;

/

What I need to do though, is pass just the email addresses into the existing email procedure to fire off emails to anyone who comes up on the list generated by the output.

This is the next part of the procedure, I have used dbms_output to test and verify that the email addresses are being generated correctly and passed into v_contact_email. Now, when I try and send the mail, only one address gets passed in by this:

send_mail.send(
ToList=>             v_contact_email,
Subject=>            'Ticket closing warning.',
Body=>               'Please note, your ticket '|| v_incno ||' will be subject to automatic closure',
FromEmail=>          'donotreply@test.com.au',
FromHost=>           'emailsrv',
SMTPServer=>         'emailsrv',);
close c1;
close c2;
End;
/

It's not sending to the several emails that are shown to be correctly passed into v_contact_email. It just sends one email out and nothing else.

Why won't the several email addresses that are looped into v_contact_email result in several emails going out, instead of just one?

How can I fix it so the:

send_mail.send(
ToList=>             v_contact_email,
Subject=>            'Ticket closing warning.',
Body=>               'Please note, your ticket '|| v_incno ||' will be subject to automatic closure',
FromEmail=>          'donotreply@test.com.au',
FromHost=>           'emailsrv',
SMTPServer=>         'emailsrv',);
close c1;
close c2;
End;
/

Part of the code correctly loops through the full result set? Instead of just getting one address and firing off one email before doing nothing else?

Answer 1

The first code you posted isn't entirely valid; it the first cursor lacks LOOP. If you fix that, the result will be:

• bunch of e-mail addresses
• bunch of incident numbers

The first loop fetches e-mail addresses, so v_contact_email contains only the last address fetched. The same goes for incident numbers.

One option is that you'd want to nest those loops, something like this (I'm using cursor FOR loops as they are easier to maintain that explicitly declared cursors, which you have to declare (as well as cursor variable(s), open, loop, take care about exiting the loop, close the cursor) - if you use cursor FOR loop, Oracle does most of those things for you.

begin
  for cur_r in (select contact_email from incidents_view where ...) loop
    for cur_i in (select incident_number from incidents_view where ...) loop
      send_mail.send(ToList  => cur_r.contact_email,
                     Subject => 'Ticket closing warning.',
                     Body    => 'Please note, your ticket '|| cur_i.incident_number ...
                    );
    end loop;
  end loop;
end;

---

But, why do you have two cursors? They look the same (except of what they select), but - FROM clauses are equal, WHERE clauses are equal ... why not using only one cursor? E.g.

begin
  for cur_r in (select contact_email, incident_number
                from incidents_view where ...
               ) loop
    send_mail.send(ToList  => cur_r.contact_email,
                   Subject => 'Ticket closing warning.',
                   Body    => 'Please note, your ticket '|| cur_r.incident_number ...
                  );
  end loop;
end;