I have a nested table of records (created as %ROWTYPE of a table) containing three elements (firstname, lastname, and email). The problem is that this table contains duplicate records that I need to remove. I am trying the following:
DECLARE
TYPE t_poc IS TABLE OF poc%ROWTYPE;
lt_poc t_poc := t_poc();
lt_poc_set t_poc := t_poc();
BEGIN
--snip (populating lt_poc with duplicate records)
lt_poc_set := lt_poc;
lt_poc_set := lt_poc_set MULTISET UNION DISTINCT lt_poc;
END;
/
The problem is that this fails with
PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_DISTINCT'
I can't do a SQL DISTINCT as lt_poc is not being populated from a database table.
Is there another method to get myself a nested table of DISTINCT records? Looks like MULTISET UNION DISTINCT can't handle tables of records, only tables of individual elements. I'm running database 11.2.0.3 Enterprise on Windows Server 2003.