PL/SQL Loop Returning One Result

1 vote

1 answer

254 views

                          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. 
                        

Asked by krebshack (111 rep)

May 24, 2018, 09:54 PM
Last activity: May 29, 2018, 03:05 PM

PL/SQL Loop Returning One Result

Related Questions