Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Compare two oracle collections

Posted on 2013-01-30
3
Medium Priority
?
407 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 78

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 78

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

972 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