Having insert statement and procedure after FORALL

Question

How can I have one insert statement and call procedure after FORALL in plsql?

I have the following in procedure

FORALL indx IN p_product.FIRST .. p_product.LAST
        INSERT INTO   my_table
              VALUES   (p_product(indx),p_product_desc(indx),p_msg);

After the insert I would like to call another procedure which inserts values into another table.

remove_dup_products(p_product(indx));

When I had tried to call the above procedure after insert statement, I am getting error

INDX must be declared

Answer 1

A FORALL statement is just that; a statement; you can only do one thing in it. You have to loop through your type again.

forall indx in p_product.first .. p_product.last
   insert into my_table
   values (p_product(indx), p_product_desc(indx), p_msg);

for indx in p_product.first .. p_product.last loop
   remove_dup_products(p_product(indx));
end loop;

It's worth nothing that you're not doing two DML statements; you're doing one and calling a procedure. You cannot therefore use FORALL twice, you have to use a regular for loop.

If you're only doing DML in the second procedure you could pass in the entire collection and then use FORALL then. You would need to declare a global variable:

create or replace package something is

   type t__product is table of product.product%type;
   t_product t__product;

   ...

and then you could reuse this everywhere

create or replace package body something is

procedure current_proc is

begin

   forall indx in p_product.first .. p_product.last
      insert into my_table
      values (p_product(indx), p_product_desc(indx), p_msg);

   remove_dup_products(p_product);

end current_proc;

-------------------------------------------------------------

procedure remove_dup_products (p_product in t_product) is

begin

    forall in p_product.first .. p_product.last
       delete ...