Hello,
I am trying to make stored procedure in what i am getting i_group_id as a list of groups seprated by semicoln like 1,2,14,17,23.
And i want list of emails based on that group. And result set will be as a list of emails seprated by semicolon.
If you know how to install that in stored procedure please help me. Appreciate your help.
Thanks.
PROCEDURE get_groups_email(i_group_id IN VARCHAR2,
x_group_email_dtl_cur OUT resultcur)
IS
x_group_email VARCHAR2(4000):=NULL;
BEGIN
FOR i IN (SELECT TRIM(emp.email) email
FROM ems.employee emp,
ems.groups_employee egrp
WHERE egrp.group_id IN (i_group_id)
AND emp.person_id = egrp.person_id) LOOP
x_group_email:= x_group_email || i.email ||';';
END LOOP;
x_group_email := RTRIM(x_group_email,';');
OPEN x_group_email_dtl_cur FOR
SELECT x_group_email
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('x_group_email:' || x_group_email);
END get_groups_email;
PROCEDURE get_groups_email(i_group_id IN VARCHAR2,
x_group_email_dtl_cur OUT resultcur)
IS
x_group_email VARCHAR2(4000):=NULL;
BEGIN
FOR i IN (SELECT TRIM(emp.email) email
FROM ems.employee emp,
ems.groups_employee egrp
WHERE egrp.group_id IN (i_group_id)
AND emp.person_id = egrp.person_id) LOOP
x_group_email:= x_group_email || i.email ||';';
END LOOP;
x_group_email := RTRIM(x_group_email,';');
OPEN x_group_email_dtl_cur FOR
SELECT x_group_email
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('x_group_email:' || x_group_email);
END get_groups_email;