This forum is currently locked. You can't register or post questions at this time. (read more)

Find Duplicates In PLSQL Collection

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

jakoboski
Member
Posts: 12
Joined: Sun Nov 03, 2013 2:28 pm

Find Duplicates In PLSQL Collection

Postby jakoboski » Wed May 28, 2014 5:59 am

Hello,

I am populating collection as

Code: Select all

for prod in ( SELECT 'K2456' AS product_id,
   'KILO-BAT' AS product_name, 5800 AS product_charged
FROM DUAL  UNION ALL
SELECT 'LG2456' AS product_id,
'LITE-BAT' AS product_name, 3560 AS product_charged
FROM DUAL UNION ALL
SELECT 'LG2456' AS product_id,
'LITE-BAT' AS product_name,3462 AS product_charged
FROM DUAL UNION ALL
SELECT 'LK551' AS product_id,
'ORD-BAT' AS product_name,1200 AS product_charged
FROM DUAL)

LOOP

str :=  'Product Id --> '
   || prod.product_id
   || '-- Product Name --> '
   || prod.product_name
   || ' -- Product Charge --> '
   || prod.product_charged; 

  my_array.extend();
  my_array(my_array.count()) := str;

END LOOP:


my_array and str are defined as

Code: Select all

str            VARCHAR2(512); 
my_array         SYS.dbms_debug_vc2coll := NEW sys.dbms_debug_vc2coll ();



What I would like to achieve is find the duplicate product_id and product_name from collection (my_array) and get the sum of product_charged for those duplicates.

How can I do this?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Find Duplicates In PLSQL Collection

Postby Tim... » Wed May 28, 2014 7:52 am

Hi.

You have not explained why the data needs to be stored in a collection. Pulling the data back into a collection, then searching that collection is much less efficient that pulling the relevant data out of the database using SQL in the first place. I would get rid of the collection and query the duplicates directly.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jakoboski
Member
Posts: 12
Joined: Sun Nov 03, 2013 2:28 pm

Re: Find Duplicates In PLSQL Collection

Postby jakoboski » Wed May 28, 2014 7:56 am

Tim,

Yes it is a good question as why do I need a collection, why not use SQL directly.

Code what I have posted is snippet, due to obvious reasons I am not incorporating complete code. I am using collection to display messages to user as I am iterating values in a loop. Thus in collection if there are duplicate records(product_id and product_name), would like to get those and sum up product_charged.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Find Duplicates In PLSQL Collection

Postby Tim... » Wed May 28, 2014 8:05 am

Hi.

In that case I have some suggestions:

1) If you stored the data in a global temporary table, rather than a collection, it would make further processing easy as you could use SQL, rather than looping through a collection. The collection seems like the wrong approach here.

2) Whether using a collection or a GTT, you should store the data in a normal record structure. By putting it into a single string, it means any further processing has to go to the effort pulling it apart again, which is just burning CPU doing string handling.

3) The processing for finding a duplicates in a collection is going to be a real pain in the ass. You are basically writing an algorithm for aggregate functions yourself. Take advantage of the database, which already has those things...

A classic flaw in people's development process is not noticing when the wrong choice has been made. It looks to me like you are in that position now. The approach you've taken to solve the problem is flawed. You can either spend time and effort trying to progress this and end up with a God-awful solution to a problem you have made for yourself, or start again and do what databases do best, which is query tables (normal or GTTs).

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jakoboski
Member
Posts: 12
Joined: Sun Nov 03, 2013 2:28 pm

Re: Find Duplicates In PLSQL Collection

Postby jakoboski » Wed May 28, 2014 8:11 am

Tim,

As I mentioned about the snippet part, what you see SQL part is altered. Originally instead of SQL, I have multi row values from a grid which are stored in collection.

Your first option is good which GTT, however I am not sure whether to store multi row grid values in GTT. Kindly suggest best approach for this and how to achieve results I am looking for?

Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Find Duplicates In PLSQL Collection

Postby Tim... » Wed May 28, 2014 8:44 am

Hi.

Seriously, I can't suggest a "best" approach when I have no idea what your code is doing... :)

When you get your data, how is it presented to you? Is it passed to you as a collection, or are you building the collection from the database yourself. If the former, then insert it into a GTT using the same query you use to build the collection. If the latter, you will need to split the data up using SUBSTR and insert it into the GTT yourself. I assume you know how to do string handling in PL/SQL.

When you have the data in a GTT, you can query it in the normal way.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jakoboski
Member
Posts: 12
Joined: Sun Nov 03, 2013 2:28 pm

Re: Find Duplicates In PLSQL Collection

Postby jakoboski » Wed May 28, 2014 12:54 pm

Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Find Duplicates In PLSQL Collection

Postby Tim... » Wed May 28, 2014 1:00 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 5 guests

cron