Solved

Compare two oracle collections

Posted on 2013-01-30
3
395 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 500 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

623 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