0

I have the following query:

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-QA-NUC'
AND pa.notify_user = 'TRUE';

Which gives me the userid's for PO authroizers in that authorization group. I can have several PO authorizers. I need to be able to email all of them.

I'm trying to create an event action in our ERP system that will run the following whenever a PO is released:

BEGIN
   command_sys.mail(from_user_name_ => 'IFSAPP', 
                    to_user_name_ => 'youremail@yourdomain.com', 
                    subject_ => 'Test subject', 
                    text_ => 'Test message body');
   COMMIT;
 END;

I can enter multiple userid's in the to_user_name section but I'm having a hard time figuring out how I can get the results of this query in the to_user_name section?

EDIT: I'm still trying this but I'm not able to get it working:

CREATE PROCEDURE a_proc
AS
cursor get_emails is
SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id) AS "EMAIL"
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';
result_ VARCHAR2(500);

BEGIN
    OPEN  get_emails;
    FETCH get_emails BULK COLLECT INTO vi_emails;
    CLOSE get_emails;

    FOR indx IN 1..vi_emails.COUNT LOOP
     command_sys.mail(from_user_name_ => 'IFSAPP', 
                    to_user_name_ => result_, 
                    subject_ => 'Test subject', 
                    text_ => 'Test message body');
    END LOOP;
END a_proc;

EDIT2:

I've updated to this:

    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) AS "EMAIL"
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 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;

Which is compiling with no errors. However, no emails are going to the users.

krebshack
  • 992
  • 1
  • 9
  • 19
  • Possible duplicate of [Fetch MULTIPLE ROWS and STORE in 1 VARIABLE - ORACLE STORED PROCEDURE](https://stackoverflow.com/questions/16967199/fetch-multiple-rows-and-store-in-1-variable-oracle-stored-procedure) – Wyatt Shipman May 23 '18 at 16:45
  • That's similar but I'm not trying to do the same thing. I don't see how that's a duplicate. – krebshack May 23 '18 at 16:51
  • I think what you are needing to do is write multiple rows or emails into a variable so that you can assign the variable to the to_user_name section. – Wyatt Shipman May 23 '18 at 16:59
  • I had tried: cursor get_emails is SELECT purchase_authorizer_api.get_userid('30', pagl.authorize_id) AS "EMAIL" 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'; result_ VARCHAR2(500); BEGIN result_ := null; FOR rec_ IN get_emails LOOP command_sys.mail(from_user_name_ => 'IFSAPP', to_user_name_ => result_, subject_ => 'Test subject', text_ => 'Test message body'); RETURN result_; – krebshack May 23 '18 at 17:16
  • So, this problem is still unresolved but since it's marked as a duplicate I won't get it answered. Thanks for nothing, Wyatt. – krebshack May 24 '18 at 21:18

1 Answers1

1
SELECT
    LISTAGG(EMAIL,',') WITHIN GROUP(ORDER BY EMAIL) AS LIST
FROM
    (
        SELECT
            'abc@abc.com' AS EMAIL
        FROM
            DUAL
        UNION
        SELECT
            'xyz@abc.com' AS EMAIL
        FROM
            DUAL
        UNION
        SELECT
            'def@abc.com' AS EMAIL
        FROM
            DUAL
        UNION
        SELECT
            'ddd@abc.com' AS EMAIL
        FROM
            DUAL
        UNION
        SELECT
            'fff@abc.com' AS EMAIL
        FROM
            DUAL
        UNION
        SELECT
            'ggg@abc.com' AS EMAIL
        FROM
            DUAL
    );

Output -

LIST
abc@abc.com,ddd@abc.com,def@abc.com,fff@abc.com,ggg@abc.com,xyz@abc.com

You can store the results of this query into a variable and pass it as the argument.

Hope this works for you.

shrek
  • 887
  • 6
  • 12