PL/SQL Loop Returning One Result

Question

I'm working with the following script:

create or replace PROCEDURE a_proc
is vi_get_emails varchar2(500);
cursor get_emails is
SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id)
FROM purch_authorize_group_line pagl join purchase_authorizer pa
ON pagl.authorize_id = pa.authorize_id
WHERE pagl.authorize_group_id = '30-PM-COM'
AND pa.notify_user = 'TRUE';

BEGIN
    OPEN  get_emails;
    FETCH get_emails  INTO vi_get_emails;
    CLOSE get_emails;

  FOR rec_ IN vi_get_emails LOOP
     command_sys.mail(from_user_name_ => 'IFSAPP', 
                    to_user_name_ => vi_get_emails, 
                    subject_ => 'Test subject', 
                    text_ => 'Test message body');
    END LOOP;
END a_proc;

This part of my script returns two values:

SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id)
FROM purch_authorize_group_line pagl join purchase_authorizer pa
ON pagl.authorize_id = pa.authorize_id
WHERE pagl.authorize_group_id = '30-PM-COM'
AND pa.notify_user = 'TRUE';

Let's call these values Bob and Bill.

I would like the second part of my script to email both Bob and Bill. However, it's only emailing Bob since Bob is the first record returned by my query. It's not emailing the two users. It's only emailing the first user returned by the query.

sticky bit · Answer 1 · 2018-05-29T15:05:47.393

You have to loop over the results from the first query. You're just fetching once from your cursor and then close it. So you only process the first line of the result.

Try something like:

CREATE OR REPLACE PROCEDURE a_proc
IS
BEGIN
  FOR email IN (SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id) email
                       FROM purch_authorize_group_line pagl
                            INNER JOIN purchase_authorizer pa
                                       ON pagl.authorize_id = pa.authorize_id
                       WHERE pagl.authorize_group_id = '30-PM-COM'
                            AND pa.notify_user = 'TRUE') LOOP
    command_sys.mail(from_user_name_ => 'IFSAPP', 
                     to_user_name_ => email.email, 
                     subject_ => 'Test subject', 
                     text_ => 'Test message body');
  END LOOP;
END a_proc;

I use an implicit cursor here (FOR email IN (SELECT ...)) to loop over the results. Of course you can do that equivalently with an explicit cursor too.

PL/SQL Loop Returning One Result

1 Answers1