1 Reply Latest reply on Jun 20, 2013 7:13 PM by rp0428

    Store procedure which get list of values separated by semicolon and return result set as a string semicolon separated strin

    1013527

      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.

      Can anybody please help me for that. Thanks in advance.

       

      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;