Remove duplicate of collection in oracle

I have this type in my package:

TYPE LIST_A_TYPE TABLE OF TABLE_A%ROWTYPE;

Then I have this procedure where I populate this collection looping a table:

procedure load_me
as

  l_list_a LIST_A_TYPE
  l_rec_a  TABLE_A%ROWTYPE;

begin

  -- init collection
  l_list_a := LIST_A_TYPE ();

  for rec in (select * from table_b)
  loop

    -- do stuff that returns a TABLE_A rowtype
    l_rec_a := populate_a(rec)

    l_list_a.extend();
    l_list_a(l_list_a.count) := l_rec_a ;

  end loop;

end load_me:

After this loop, I have my collection full of l_list_a.

I need a procedure to remove duplicates of this list based on some columns of its definition (table).

I thought to use the SET function but keeps giving me an error.

The table TABLE_A has 30 columns. I got a ‘key’ of 5 columns to determine if the record could be duplicated or not…

Any tips?

Thanks

Answer

In general you have to create a MAP MEMBER FUNCTION for the record. I don’t think it can be based on TABLE_A%ROWTYPE, you have to define the type manually.

See this example. Object STATIC_ROUTE has 6 attributes and IP_ADDRESS,NETWORK_MASK,NEXT_HOP is the key.

CREATE OR REPLACE TYPE STATIC_ROUTE AS OBJECT (
    ROUTE_INDEX NUMBER,
    IP_ADDRESS VARCHAR2(15),
    NETWORK_MASK VARCHAR2(15),
    NEXT_HOP VARCHAR2(15),
    REDISTRIBUTE VARCHAR2(5),
    ROUTE_METRIC NUMBER,
MAP MEMBER FUNCTION getId RETURN VARCHAR2)
/

CREATE OR REPLACE TYPE BODY STATIC_ROUTE IS 

MAP MEMBER FUNCTION getId RETURN VARCHAR2 IS
BEGIN
    RETURN SELF.IP_ADDRESS||'-'||SELF.NETWORK_MASK||'-'||SELF.NEXT_HOP;
END;

END;
/

CREATE OR REPLACE TYPE STATIC_ROUTES  AS TABLE OF STATIC_ROUTE
/

Now you can use SET function on STATIC_ROUTES variables.

However, you should try to get the result in a single SLQ statement. It could be similar to this one:

WITH t AS (
    SELECT DISTINCT 
        id_col_1, id_col_2, id_col_3, id_col_4, id_col_5, 
        FIRST_VALUE(col_6) OVER (PARTITION BY id_col_1, id_col_2, id_col_3, id_col_4, id_col_5 ORDER BY ROWID) col_6,
        FIRST_VALUE(col_7) OVER (PARTITION BY id_col_1, id_col_2, id_col_3, id_col_4, id_col_5 ORDER BY ROWID) col_7,
        FIRST_VALUE(col_8) OVER (PARTITION BY id_col_1, id_col_2, id_col_3, id_col_4, id_col_5 ORDER BY ROWID) col_8,
        ...
        FIRST_VALUE(col_30) OVER (PARTITION BY id_col_1, id_col_2, id_col_3, id_col_4, id_col_5 ORDER BY ROWID) col_30
    FROM TABLE_A)
SELECT LIST_A_TYPE(id_col_1, id_col_2, id_col_3, id_col_4, id_col_5, id_col_6, ... col_30)
BULK COLLECT INTO l_list_a
FROM t;