eliminating duplicates from plsql associative arrays

Question

I want to avoid storing duplicate values in plsql associative array. For example: When processing and adding values in a loop, if the array already contains the element value, that value should not be stored recognizing that such element already exists in the plsql table.

For ex: if the array has stored the word USA during a loop processing, then if conditions become such that USA again is the answer in the next iteration of the loop, then it should not be stored as it exists already.

Likewise, if GER does not exist(again,based on some lookup), then it gets stored for the first time, but not subsequently.

I tried looking up "exists", but it expects a parameter element number/position or i could be wrong in its usage. Is there a way to check the entire collection and determine if a value exists before storing it?

• Casey

Answer 1

One option would be to change the definition of your associative array so that the value you are trying to ensure is unique is the key. Something like

DECLARE
  TYPE country_aat IS TABLE OF boolean
    INDEX BY varchar2(3);
  l_countries country_aat;
BEGIN
  FOR i IN (<<query that returns country codes>>)
  LOOP
    l_countries( i.country_code ) := true;
  END LOOP;

At this point, you can iterate through the keys of l_countries and see the distinct country codes that were found.

Another option would be to use a nested table and use a MULTISET operation. For example

DECLARE
  TYPE country_nt IS TABLE OF varchar2(3);
  l_countries country_nt;
BEGIN
  <<populate l_countries>>

  l_countries := l_countries MULTISET INTERSECT DISTINCT l_countries;
END;

The last step will eliminate the non-distinct entries. One of the two right-hand tables could also be an empty collection of country_nt-- that would probably be more efficient but would require declaring an additional local variable.