Compare two oracle collections

Hi Experts,

I need to compare two collections.... here are the details

PACKAGE_ONE - SPEC
TYPE rec_one IS RECORD(abc       VARCHAR2(20),
                       xyz         NUMBER,
                       code         VARCHAR2(4000),
                       code_type VARCHAR2(20));
TYPE tab_one IS TABLE OF rec_one INDEX BY BINARY_INTEGER;        


PACKAGE_TWO - SPEC
TYPE rec_two IS RECORD
      (id         name.id%TYPE,
       code_type  name.code_type%TYPE,
       code       name.code%TYPE);
TYPE tab_two IS TABLE OF rec_two INDEX BY BINARY_INTEGER;



PACKAGE_THREE - BODY

PACKAGE_ONE.TAB_ONE.count - 5;
PACKAGE_TWO.TAB_TWO.count - 3;

I need to compare these two collections for each of row of PACKAGE_TWO.TAB_TWO.code to PACKAGE_ONE.TAB_ONE.code  for SAME code_type;
if they are same then do nothing, else add that record in PACKAGE_TWO.TAB_TWO at the end of PACKAGE_ONE.TAB_ONE.

please advice gurus....

I have tried this but not tested yet... I'm  looking for a smarter approach
declare
  v_idx  binary_integer;
  v_Ctr  binary_integer;  
begin
  v_Ctr   := nvl(PACKAGE_TWO.TAB_TWO.last,0);
  v_idx   := PACKAGE_TWO.TAB_TWO.first();
  while (v_idx is not null)
  loop
     for i in 1..PACKAGE_ONE.TAB_ONE.count
     loop              
       if PACKAGE_ONE.TAB_ONE(i).code_type = PACKAGE_TWO.TAB_TWO(v_idx).code_type and
          nvl(upper(substr(PACKAGE_ONE.TAB_ONE(iCtr).codes,1,200)),'~~~') = nvl(upper(substr(PACKAGE_TWO.TAB_TWO(v_idx).code,1,200)),'@@@') then
          null;
       else  
          ictr := iCtr + 1;
          PACKAGE_ONE.TAB_ONE(v_Ctr).code        := substr(PACKAGE_TWO.TAB_TWO(v_idx).code,1,2000);
          PACKAGE_ONE.TAB_ONE(v_Ctr).code_type    := nvl(PACKAGE_TWO.TAB_TWO(v_idx).code_type, 'ABC');
       end if;
     end loop;
     v_idx := PACKAGE_TWO.TAB_TWO.next(v_idx);          
  end loop;
end;
loginboyAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I don't have 10g to test on anymore so cannot provide a working example.

I'm also not sure on the 'most efficient' method.  This will likely require taking all the suggestions you receive and test them individually.

Check out the following article.

Steven Feuerstein is pretty much the standard when it comes to PL/SQL so I would pretty much take what he posts as gospel.

The following article mentions:
Part 1 in a series that looks at enrichments to PL/SQL in Oracle Database 10g.

Towards the bottom it has:
When Uniqueness Counts

The final MULTISET function to introduce is SET . SET (without a MULTISET as prefix) offers the ability to remove duplicate entries from your nested table. In this way, it is similar to the SQL DISTINCT aggregate function.

http://www.oracle.com/technetwork/issue-archive/o53plsql-083350.html
0
 
slightwv (䄆 Netminder) Commented:
>>if they are same then do nothing, else add that record in PACKAGE_TWO.TAB_TWO at the end of PACKAGE_ONE.TAB_ONE.

Technically can't you just set tab_two equal to tab_one?

Actual sample data and expected results would help.

Also your database versions.


Without knowing your data it's hard to get what you are after.

Can you merge the two and ten just remove dupes?

If so and you are running 11g, the SET operator should work:
set       removes duplicates from a collection and thereby creates a set of unique values

http://www.datadisk.co.uk/html_docs/oracle/collections.htm

There is an example in the link.
0
 
loginboyAuthor Commented:
Hii  slightwv,

Not all the columns/rows will be same, only few rows(codes) are likely to be same. Hence want to compare one by one.

let me try for sample data

tab_one
A
B
C
H

tab_two
C
D
E

I have to compare each of the code and if it's already exists then do not copy that row...

Expected Output

tab_one
A
B
C
H
D
E

Version - 10.2.0.5.0

I can merge all and then remove duplicates, but is that efficient enough? if so, could you please give an idea how I should remove duplicates from a collection...

Checking out the link, but not sure I can go ahead with it as it says from 11g onwards...

Thanks.. please help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.