populate unique values in a collection

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

populate unique values in a collection

Postby yairk30 » Mon Jun 25, 2012 7:56 pm

Hey Tim and how are you!
I need to populate a collection with values from a table in order that every brand of value from the table will be represented 1 time only in the collection.
For example , let say we have a table "agreement" with one column "agreement_num" (varchar2). This column contain a 200 records with max 3 brand of values ("agreem num 1" , "agreem num 2" etc.).
Now , when I populate the collection I want it to contain 1 item for every different value from the table.
Agreement_arr(1) = "agreem num 1" ,
Agreement_arr(2) = "agreem num 2"
Etc.
Note: the logic in order to obtain the data from the table is difficult.
I canot just run with a cursor like
Code: Select all
select distinct  agreement_num  from agreement
.
I need a way for looking up inside the array in order to check if the new value fetching from the table is already represented in the array or not.
(running a loop on the array to compare with the value fetched from the table every time , is not a good idea of course.)
I need something similar to exists method (but for checking existence of a string values in the array , not just a n items) .
Hope I made myself clear.

I'm on 8i version.

Thanks,
yair
yairk30
Member
 
Posts: 35
Joined: Thu Apr 20, 2006 6:05 am

Re: populate unique values in a collection

Postby Tim... » Tue Jun 26, 2012 6:52 am

Hi.

OK. You can't figure out a way to pull the right rows directly. In that case, use your complex logic to populate a global temporary table.

http://www.oracle-base.com/articles/8i/ ... tables.php

From there you can do a select distinct into your collection. I think this will probably be a faster that putting the data directly into the collection, then trying to filter it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: populate unique values in a collection

Postby yairk30 » Tue Jun 26, 2012 4:02 pm

100%

thanks!

yair
yairk30
Member
 
Posts: 35
Joined: Thu Apr 20, 2006 6:05 am

Re: populate unique values in a collection

Postby Tim... » Tue Jun 26, 2012 6:50 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest