Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Merging Cursors in PL/SQL

Posted on 2007-04-10
8
Medium Priority
?
7,930 Views
Last Modified: 2013-12-18
Is there a way to merge two or more cursors together into a single cursor in Oracle 10g PL/SQL?

I have multiple cursors that contain thousands of records.  Each cursor has an ID that ties the data together.  I then want to merge all of the cursors into a single cursor so that I can then perform further processing on each record in the cursor.  

I need to do this in PL/SQL within Oralce 10g.

Any ideas?
0
Comment
Question by:ccorrente
[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
8 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 18884575
There is no direct way to merger cursors. However it is possible to open both cursors

declare
c1t c1%rowtype;
c2t c2%rowtype;
BEGIN
open c1;
open c2;
LOOP
fetch c1 into c1t;
--  code exit conditions here
fetch c2 into c2t;
--  code exit conditions here
-- program logic
END LOOP;
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18886664
Hi ccorrente,

If the SQLs in the 2 cursors are actually quite similar, another possible way is to write another cursor that unions the SQL in the 2 cursor or have a SQL with the conditions that will satisfy both cursors. Take note of any difference in the colmns that is selected in the 2 cursors and you need to adjust this merge SQL according such that they will contain the same sets of columns.

UNION method:
(Select ..... ) UNION (Select ....)

Merge condition :
Select ..
FROM ...
WHERE (conditions from cursor1)
OR (conditions from cursor2)

Which method is workable would depend on the kind of SQL that you have in the cursor.

Cheers,
NicksonKoh
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18887212
This depends on the SQL of your 2 cusors, can u give me a idea , how these sql look like?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 21

Accepted Solution

by:
oleggold earned 2000 total points
ID: 18893433
Union is a nice thing but in 10G You have also an option of using the CURSOR TAG LIKE THIS:
CURSOR curs_orders IS
      SELECT order_number,
             CURSOR ( SELECT order_line_amt
                        FROM order_lines ol
                       WHERE ol.order_number = orders.order_number )
        FROM orders;
Merge Your cusor on Your key like in this example the key is the order_number
0
 
LVL 21

Expert Comment

by:oleggold
ID: 18894172
NOT ONLY THIS LOOKS MORE ELEGANT AS IT SEEMS TO ME ,THIS IS ALSO MUCH MORE EFFICIENT,INSTEAD OF FETCHING 1000 ROWS PER EACH CURSOR WITH 2 SELECTS AND UNION/MAY BE SOME NESTED LOOP JOIN ON THAT IT WILL FETCH ONLY THE FIRST SELECT + ONLY RELEVANT ROWS FOR THE SECOND SELECT.
0
 
LVL 3

Expert Comment

by:biswaranjan_rath
ID: 18895881
But I would suggest gather the queries of the cursor and create a NEW cursor using those queries. The advantage of this will be: a) it is simple to understand b) easy to implement and to work with c) comparatively take less memory space.
And with cursor merging there are few limitations like: 1) Result cursor may have huge data since it will merge the other cursor data and have cartesian product. Evene if there is not cartesian product, still I assume the resultset will be huge. 2) merging will complicate the understanding. 3) it will be error-prone.

If you still want to go for merging, then there are a lot of ways as other friends mentioned above.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

715 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