Oracle Apex procedure using loop

Question

I am trying to get the user_id and group_id for individual user.

I have used user's email in loop because so many users are there, but I need the loop should take one by one, currently its taking all mail ids like : abinnaya.moorthy@abc.com,abinnaya.moorthy@def.com.

Because of this the select query is not returning any value.

The select query should return the value one by one by taking the email id from loop.

code:

DECLARE    
    L_USERS  varchar2(1000);
    l_org_group_id varchar2(1000);
    l_user_id varchar2(1000);
    l_api_body varchar2(1000);
    l_retry_after number;
    l_status number;
    L_NOT_PROVISIONED_USERS varchar2(1000);
    l_success boolean;
    l_user varchar2(1000);
BEGIN

  FOR I IN
    (Select REQUESTORS_NAME into L_USER 
    from Request 
    where Request_Status = 'Approved' 
    and Provisioning_Status is NULL )
  LOOP
    L_USER:= L_USER ||','||I.REQUESTORS_NAME;

    select GROUP_ID INTO l_org_group_id 
    from WORKSPACE_GROUP 
    where LOWER(email)=(L_USER);

    select USER_ID into l_user_id 
    from slackdatawarehouse.users
    where lower(email) = lower(L_USER);  

    DBMS_OUTPUT.PUT_LINE(l_user_id);

    if l_user_id is null then
       l_not_provisioned_users := l_not_provisioned_users||','|| L_USER;
    else
       l_api_body := l_api_body || '{"value" :"'||l_user_id ||'"},'; 
       l_users := l_users||','||l_user_id;

    end if;
  end loop;

end;

Help me to get the user email one by one and pass it in select query to get the groupid and user id.

Answer 1

Do it as below:

 DECLARE
         L_USERS                       VARCHAR2 (1000);
         L_ORG_GROUP_ID                VARCHAR2 (1000);
         L_USER_ID                     VARCHAR2 (1000);
         L_API_BODY                    VARCHAR2 (1000);
         L_RETRY_AFTER                 NUMBER;
         L_STATUS                      NUMBER;
         L_NOT_PROVISIONED_USERS       VARCHAR2 (1000);
         L_SUCCESS                     BOOLEAN;
         L_USER                        VARCHAR2 (1000);

         CURSOR EMAIL_IDS
         IS
              SELECT REQUESTORS_NAME L_USER
                FROM REQUEST
               WHERE REQUEST_STATUS = 'Approved'
                     AND PROVISIONING_STATUS IS NULL;
    BEGIN
         FOR I IN EMAIL_IDS
         LOOP
              SELECT GROUP_ID
                INTO L_ORG_GROUP_ID
                FROM WORKSPACE_GROUP
               WHERE LOWER (EMAIL) = LOWER (I.L_USER);

              SELECT USER_ID
                INTO L_USER_ID
                FROM SLACKDATAWAREHOUSE.USERS
               WHERE LOWER (EMAIL) = LOWER (I.L_USER);

              DBMS_OUTPUT.PUT_LINE (L_USER_ID);

              IF L_USER_ID IS NULL THEN
                   L_NOT_PROVISIONED_USERS :=
                                         L_NOT_PROVISIONED_USERS || ',' || I.L_USER;
              ELSE
                   L_API_BODY :=
                                L_API_BODY || '{"value" :"' || L_USER_ID || '"},';
                   L_USERS := L_USERS || ',' || L_USER_ID;
              END IF;
         END LOOP;
   EXCEPTION
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;