Solved

Compare two oracle collections

Posted on 2013-01-30
3
384 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
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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.
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…

685 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