OraFAQ Forum: SQL & PL/SQL » How to remove duplicates from collection

Home » SQL & PL/SQL » SQL & PL/SQL » How to remove duplicates from collection (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to remove duplicates from collection [message #500850]

Thu, 24 March 2011 02:03

aasetty
Messages: 4
Registered: November 2007
Location: San jose

Junior Member

Collection Guru's

Can you help me to remove duplicates from my collection(table type).

Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.

Object creation

create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10))
insert into testingtype_table values (1124,2424,'cbaaa');
insert into testingtype_table values (1124,2424,'cbaaa');

create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10));

create or replace type testingtype_tab as table of testingtype_obj;

Code Block
declare
l_testingtype_tab testingtype_tab := testingtype_tab();
begin
select distinct testingtype_obj(ordernumber
,org_id
,company_name)
bulk collect into l_testingtype_tab
from testingtype_table;
end;

If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error

ORA-22950: cannot ORDER objects without MAP or ORDER method

Any help would be much appreciated...

thanks in advance

Report message to a moderator

Re: How to remove duplicates from collection [message #500855 is a reply to message #500850]

Thu, 24 March 2011 02:10

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

SQL> declare
  2    l_testingtype_tab testingtype_tab := testingtype_tab();
  3  begin
  4    select testingtype_obj(ordernumber,org_id,company_name) 
  5    bulk collect into l_testingtype_tab
  6    from ( select distinct ordernumber,org_id,company_name 
  7           from testingtype_table ); 
  8  end;
  9  /

PL/SQL procedure successfully completed.

Regards
Michel

Report message to a moderator

Re: How to remove duplicates from collection [message #500857 is a reply to message #500855]

Thu, 24 March 2011 02:23

aasetty
Messages: 4
Registered: November 2007
Location: San jose

Junior Member

Thanks very much Michel for the quick response.
This was my first post in OraFAQ. Will keep this in mind going forward

Thanks Again
Arvind

Report message to a moderator

Re: How to remove duplicates from collection [message #501022 is a reply to message #500857]

Fri, 25 March 2011 02:35

_jum
Messages: 577
Registered: February 2008

Senior Member

You can also use the SET-Operator, to make the collection distinct:

...
  SELECT SET (l_testingtype_tab) INTO l_testingtype_tab FROM DUAL;
...

Report message to a moderator

Re: How to remove duplicates from collection [message #501023 is a reply to message #501022]

Fri, 25 March 2011 02:41

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, but why first get the duplicates to then remove them? Useless work and memory consumption (in this case).

Regards
Michel

Report message to a moderator

Previous Topic:	Stored Procedure for returning source of objects similar to sp_helptext by MS SQL Server
Next Topic:	Problem with parameter

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 31 04:25:55 CDT 2025