For loop values not passed to a function

Question

I have the following in procedure

create or replace procedure test_proc (
   p_product_holder  varchar2,
   p_products type_products -- this in type of nested table with parameters of product_no and product_catalogue
   ) 
   AS
   msg_body  VARCHAR2 (32767);
begin

   for i in 1..p_products.count loop
   msg_body :=
               myfunc(p_products(i).product_no,   p_products(i).product_catalogue);

   end loop;

    procedure_mail (
               msg_from        => 'PRODMASTER',
               msg_to          =>  p_product_holder,
               msg_subject     => 'Test',
               body_msg        => msg_body);
end;
/

The above procedure is executed as

for holder in (
     select pm.product_holder
           , cast(
                collect(
                   product_table(pm.product_no,pm.product_catalogue)
                   order by pm.product_catalogue
                          , pm.product_no
                ) as t_prod_cat_no_table
             ) product_cats_nos 
        from product_master pm
       group by pm.product_holder
       order by pm.product_holder
   ) loop
      test_proc(         
        holder.product_holder,
        holder.product_cats_nos
      );
   end loop;

The issue I am facing in test_proc is only the last value in for loop is being passed to msg_body. Ideally would like to pass all the values in loop to be passed to msg_body.

How can I do this?

Answer 1

for i in 1..p_products.count loop msg_body := myfunc(p_products(i).product_no, p_products(i).product_catalogue);

end loop;

In your code, you are passing the msg_body value to the procedure outside the loop, thus, msg_body variable will only store the last value fetched from the loop. If you move the procedure_mail inside the loop, you will be able to loop it through all the values of msg_body.

Modify the code to -

for i in 1..p_products.count loop
   msg_body :=
               myfunc(p_products(i).product_no,   p_products(i).product_catalogue);

       procedure_mail (
               msg_from        => 'PRODMASTER',
               msg_to          =>  p_product_holder,
               msg_subject     => 'Test',
               body_msg        => msg_body);

end loop; -- see this end loop encloses the procedure_mail

Update

You could append all the values of msg_body from the loop, -

for i in 1..p_products.count loop
       msg_body := msg_body || chr(10) ||
                   myfunc(p_products(i).product_no,   p_products(i).product_catalogue);
end loop;

procedure_mail (
               msg_from        => 'PRODMASTER',
               msg_to          =>  p_product_holder,
               msg_subject     => 'Test',
               body_msg        => msg_body);

chr(10) would add a line feed to next values in the loop for msg_body.