Reset associative array in PL / SQL?

Question

This is one of the “should be the best way” questions. Let me customize the problem, then I will give you my hacked solution, and perhaps you can offer a better solution. Thanks!

Let's take this little tidbit of PL / SQL

DECLARE TYPE foo_record IS RECORD (foo%type, bar%type); TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_INTEGER; arr_foos foo_records; CURSOR monkeys is SELECT primates FROM zoo; row_monkey monkeys%rowtype; BEGIN FOR row_monkey IN monkeys loop /* at this point in each iteration I need to have the associative array arr_foos in its original state. if this were java, I'd declare it right here and its scope would be limited to this iteration. However, this is not java, so the scope of the array is effectively global and I can't have one iteration data meddle with the next. */ null; END LOOP; END; 

It makes sense? I basically need to reset this for something. If it were a number starting from zero, I could just say the number: = 0; at the top of each iteration and run with it. But this is not a number, it is a type that I can simply reset with clean: = 0.

Anyway, to my hack:

 DECLARE TYPE foo_record IS RECORD (foo%type, bar%type); TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_INTEGER; arr_foos foo_records; arr_foos_reset foo_records; CURSOR monkeys is SELECT primates FROM zoo; row_monkey monkeys%rowtype; BEGIN FOR row_monkey IN monkeys loop arr_foos := arr_foos_reset; null; END LOOP; END; 

I thought that if I manage to keep a member of the same type in its original state, then I can simply return the working variable back to what the original value has. And, surprisingly, this works (I think.) But there has to be a better way. Can anyone help?

T'anks!

Answer 1

The easiest way:

 arr_foos.Delete(); 

Another way is to declare a variable inside the FOR loop. Thus, it will be recreated for each pass.

Like this:

 DECLARE TYPE foo_record IS RECORD (foo%type, bar%type); TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_INTEGER; CURSOR monkeys is SELECT primates FROM zoo; row_monkey monkeys%rowtype; BEGIN FOR row_monkey IN monkeys loop DECLARE arr_foos foo_records; BEGIN null; END; END LOOP; END; 

Answer 2

Are you going to read the data from the zoo table into the collection? Then there is a better way:

 DECLARE type foos_ts is table of zoo.foo%type index by pls_integer; foos foos_t; BEGIN select foo bulk collect into foos from zoo; ... END; 

Bulk collection automatically clears a collection before retrieving, and it works faster than reading line by line in a loop. Unfortunately, it does not work with records, so you will need several PL / SQL tables for each field.

You can find more information here: Retrieving query results in a collection with a BULK COLLECT clause