Merging Cursors in PL/SQL

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?
ccorrenteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MohanKNairCommented:
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
NicksonKohCommented:
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
ramumorlaCommented:
This depends on the SQL of your 2 cusors, can u give me a idea , how these sql look like?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

oleggoldCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
oleggoldCommented:
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
biswaranjan_rathCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.