PL/SQL：将查询结果传入邮件API批量发送邮件问题求助

Let's work through why your stored procedures aren't sending emails, then build a working version that reaches all approvers in your target authorization group.

Issues in Your First Procedure #

Your first attempt uses BULK COLLECT but has two critical gaps:

• You never declared the vi_emails collection variable, so the procedure can’t store the list of user IDs it fetches.
• You reference result_ in the mail call but never assign any value to it—so even if the cursor worked, there’s no recipient to send to.

Issues in Your Second Procedure #

In the second version:

• You fetch only one user ID into vi_get_emails, then immediately close the cursor. When you try to loop over get_emails after closing it, the loop won’t run at all.
• Even if the loop did execute, you’d only send an email to that single fetched user, not all approvers.

Working Stored Procedure #

Below are two tested solutions—pick the one that fits how your ERP’s command_sys.mail works.

Option 1: Send Individual Emails to Each Approver #

This loops through every approver and sends them a separate email:

CREATE OR REPLACE PROCEDURE send_po_approval_emails
IS
    CURSOR get_approver_userids IS
        SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id) AS user_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';
    v_user_id VARCHAR2(100); -- Adjust length to match your ERP's user ID format
BEGIN
    OPEN get_approver_userids;
    LOOP
        FETCH get_approver_userids INTO v_user_id;
        EXIT WHEN get_approver_userids%NOTFOUND;
        
        -- Send email to the current approver
        command_sys.mail(
            from_user_name_ => 'IFSAPP',
            to_user_name_   => v_user_id,
            subject_        => 'PO Release: Action Required',
            text_           => 'A purchase order has been released and needs your approval.'
        );
    END LOOP;
    CLOSE get_approver_userids;
    
    COMMIT; -- Confirm with your ERP if this commit is required
EXCEPTION
    WHEN OTHERS THEN
        -- Optional: Add error logging here (e.g., write to a log table)
        RAISE; -- Re-throw the error to make it visible in your ERP's event logs
END send_po_approval_emails;
/

Option 2: Send a Single Email to All Approvers (If Supported) #

If your command_sys.mail accepts comma-separated user IDs in the to_user_name_ parameter, concatenate all approvers first:

CREATE OR REPLACE PROCEDURE send_po_approval_emails
IS
    v_to_users VARCHAR2(1000); -- Increase length if you have many approvers
BEGIN
    -- Combine all approver IDs into a comma-separated string
    SELECT LISTAGG(purchase_authorizer_api.get_userid('30', pagl.authorize_id), ',') WITHIN GROUP (ORDER BY pagl.authorize_id)
    INTO v_to_users
    FROM (
        SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id) AS user_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'
    );
    
    -- Only send if there are approvers to notify
    IF v_to_users IS NOT NULL THEN
        command_sys.mail(
            from_user_name_ => 'IFSAPP',
            to_user_name_   => v_to_users,
            subject_        => 'PO Release: Action Required',
            text_           => 'A purchase order has been released and needs your approval.'
        );
        COMMIT;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle case where no approvers exist (optional: log or skip)
        NULL;
    WHEN OTHERS THEN
        RAISE;
END send_po_approval_emails;
/

Final Checks to Ensure Success #

• Variable Lengths: Adjust VARCHAR2 lengths to match your ERP’s user ID and email list limits.
• Permissions: Confirm the IFSAPP user has access to execute command_sys.mail and read the purchase authorization tables.
• Error Logging: Add logging to the exception block (e.g., inserting into a custom error table) to debug any future issues.

内容的提问来源于stack exchange，提问作者krebshack