Loop over list and insert into table

Question

I want to take value from one table and throw into another table. I have function where Im doing a bulk collect into a list. A List of beans.

     FUNCTION get_things_info ( p_part_id IN NUMBER)
  RETURN bean_list
IS
  thing_list  bean_list;
  BEGIN

  SELECT thing_bean (id, file_name, file_type, dbms_lob.getlength(thing), auditable)
  BULK COLLECT INTO thing_list
  FROM part_things
  WHERE part_id = p_part_id;

  RETURN thing_list;

  END get_things_info_by_id;

I want to take that list, iterate over it and put in a deleted table with the same data types. I have a procedure that does an insert based off some java code:

PROCEDURE insert_thing(p_thing_bean IN OUT NOCOPY file_thing_bean, p_user_id IN NUMBER)
   IS
   BEGIN


  INSERT INTO deleted_part_things
    (id, part_id, file_name, file_type, thing, editable)
  VALUES ( p_thing_bean.id, 
          p_thing_bean.parent_id, 
          p_thing_bean.file_name, 
          p_thing_bean.file_type,
          p_thing_bean.attachment, 
          p_thing_bean.editable);

   END insert_thing;

It does not have to use this procedure. I just need to know how to loop over the list I got back from the first function and insert into the deleted_part_thing table

Ollie · Accepted Answer · 2012-02-15T14:26:10.053

You could use FORALL This would then iterate through the collection supplied as a parameter inserting the records as required, I have given you a similar example you can amend to suit your needs:

PROCEDURE insert_from_list (
   p_bean_list IN bean_list
)
IS
BEGIN
   FORALL x IN INDICES OF p_bean_list
      INSERT INTO deleted_hot_part_attachments
      (id, hot_part_id, file_name, file_type, attachment, auditable)
      VALUES (
         p_bean_list(x).id,
         p_bean_list(x).parent_id,
         p_bean_list(x).file_name,
         p_bean_list(x).file_type,
         p_bean_list(x).attachment,
         p_bean_list(x).auditable
      );
      --
      COMMIT;
END insert_from_list;

Hope it helps...

EDIT: If you are using 10g or earlier, you'll need to pull the records into a collection that is the same structure as the table to insert into before then calling:

FORALL x IN INDICES OF <new_collection>
   INSERT INTO deleted_hot_part_attachments
   VALUES <new_collection>(x);

So if the structure of deleted_hot_part_attachments matches exactly the collection type bean_list then your FORALL would be:

FORALL x IN INDICES OF p_bean_list
   INSERT INTO deleted_hot_part_attachments
   VALUES p_bean_list(x);

If it does not then you'll need something like:

PROCEDURE insert_from_list (
   p_bean_list IN bean_list
)
IS
   -- Declare collection to hold table values
   TYPE dhpa_tabtype IS TABLE OF deleted_hot_part_attachments%ROWTYPE
        INDEX BY PLS_INTEGER;
   dhpa_tab dhpa_tabtype;
BEGIN
   -- Loop through the bean list collection populating the 
   -- new dhpa_tab collection with the required values
   FOR i IN p_bean_list.FIRST .. p_bean_list.LAST
   LOOP
      dhpa_tab(i).id         := p_bean_list(i).id;
      dhpa_tab(i).parent_id  := p_bean_list(i).parent_id;
      dhpa_tab(i).file_name  := p_bean_list(i).file_name;
      dhpa_tab(i).file_type  := p_bean_list(i).file_type;
      dhpa_tab(i).attachment := p_bean_list(i).attachment;
      dhpa_tab(i).auditable  := p_bean_list(i).auditable;
   END LOOP;

   -- Populate the table using the new dhpa_tab collection values
   FORALL x IN INDICES OF dhpa_tab
      INSERT INTO deleted_hot_part_attachments
      VALUES dhpa_tab(x);

   --
   COMMIT;
END insert_from_list;

OK, then I'll add an edit to my answer or you'll get PLS-00436. — Ollie, Feb 15 '12 at 14:19
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records — Doc Holiday, Feb 15 '12 at 19:26
INSERT INTO deleted_hot_part_attachments (id, hot_part_id, file_name, file_type, attachment, auditable) — Doc Holiday, Feb 15 '12 at 19:26
@DocHoliday, did you follow the second example (the 10g example) where I created a new type to hold the collection records? — Ollie, Feb 16 '12 at 08:59

score 0 · Answer 2 · answered Feb 15 '12 at 14:45

0

A more old school approach to looping through collections would be something like:

declare
  type t_tab is table of varchar2(10);
  tab t_tab;
  tab_idx pls_integer;

begin
  -- populate tab (dense at first)
  select 'val' || level
  bulk collect into tab
  from dual connect by level <= 20;

  -- make tab sparse (just for fun)
  tab.delete(3); -- remove 3rd element
  tab.extend(5); -- make space for 5 more elements
  tab(25) := 'val25';

  -- LOOP through table contents
  tab_idx := tab.first;
  loop
    exit when tab_idx is null;
    dbms_output.put_line('Element ' || tab_idx || ' is ' || tab(tab_idx));
    tab_idx := tab.next(tab_idx);
  end loop;

end;

Nice thing is that you don't have the same restrictions with forall

answered Feb 15 '12 at 14:45

tbone

15,107
3
33
40

1

how would you then insert the data? FORALL would be quicker than an RBAR `FOR` loop. I can't see in your code where you are inserting into the table? – Ollie Feb 15 '12 at 14:55
@Ollie nothing against your code, just a different way. My code is just an example of the looping approach. Instead of dbms_output, could easily be an insert statement. – tbone Feb 15 '12 at 15:17
I totally understand but my point was that in my code I also used an "old school" loop to iterate through the collection populating the new collection before then using the FORALL for an efficient insert. I could have issued an insert per loop iteration to avoid the FORALL but it would result in painfully slow execution, hence the FORALL is the most efficient solution, your code appears to only perform half the job the OP required with the most expensive part (the insert) missing. You answer then says it avoids the FORALL restrictions but I just wanted to know how? – Ollie Feb 15 '12 at 15:28
@Ollie well, I presented my LOOPING construct because if Doc did need to avoid forall restrictions, and used your old school loop with a sparse table, he would get errors (your first..last assumes a dense array, try deleting an element to see). But again, I wasn't saying "my code is better", just another OPTION for the poster. – tbone Feb 15 '12 at 15:38
sparse collections can be a bit of a pain in the preverbial. Fair point. I did assume a dense collection due to Doc's method of generating it (`SELECT INTO`) but elements could potentially be removed along the way. Cheers. – Ollie Feb 15 '12 at 15:47

Loop over list and insert into table

2 Answers2

Linked