?
Solved

Compare two oracle collections

Posted on 2013-01-30
3
Medium Priority
?
400 Views
Last Modified: 2014-02-19
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;
0
Comment
Question by:loginboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38837737
>>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
 

Author Comment

by:loginboy
ID: 38837786
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 38837885
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

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question