Home » SQL & PL/SQL » SQL & PL/SQL » Wrapping multiple cursors resultset into one? (merged 2)
Wrapping multiple cursors resultset into one? (merged 2) [message #410198] Thu, 25 June 2009 11:25 Go to next message
h0003192
Messages: 4
Registered: June 2009
Location: UK
Junior Member
Hi all,

Thanks for reading. This is my first time posting.

I have a question that really needs your insight. Here is the background info. I am trying to modularize some very clumsy yet repetitive PL/SQL statements into more readable ones.

here is the original codes:
PROCEDURE   proc (
         Pmycursor OUT mycursor) -- Use cursor
         IS
BEGIN

    OPEN Pmycursor FOR
    (...very clumsy SELECT query)

    UNION ALL
    (...another clumsy SELECT query)

    UNION ALL
    (...another clumsy SELECT query)

END proc;


The above three SELECT queries are 99% the same, the only difference is that one of the tables within the query is different. As a result. I tried to modularize it with:

PROCEDURE sub_proc (
         table_name IN VARCHAR2,
         cursor1 OUT mycursor) -- Use cursor
         IS
BEGIN

    OPEN cursor1 FOR
    'SELECT * FROM '  || table_name;

END sub_proc;

PROCEDURE proc (
         cursor1 OUT mycursor,
         cursor2 OUT mycursor,
         cursor3 OUT mycursor,
         Pmycursor OUT mycursor) -- Use cursor
         IS
BEGIN

       sub_proc('table1',cursor1);
       sub_proc('table2',cursor2);
       sub_proc('table3',cursor3);

END sub_proc;



everything seems to work except now I have three cursors with three resultset with exactly the same table schema. Any idea how to wrap up these three resultset into one and output that?

Thanks!
Michael
Re: Wrapping multiple cursors resultset into one? (merged 2) [message #410203 is a reply to message #410198] Thu, 25 June 2009 12:00 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd stick to one ref_cursor and avoid dynamic SQL if at all possible.
It'll be easier to manage and probably faster.

What you might want to try is unioning the three different tables in an inline view and have the rest of the SQL that doesn't change only once.
but without more details it's hard to say for sure.
Re: Wrapping multiple cursors resultset into one? (merged 2) [message #410261 is a reply to message #410203] Thu, 25 June 2009 22:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
go back to the one cursor

Why do you call it clumsy anyway. If a sql is clumsy, seems to me it will be just as clumsy all by itself as it would be when stacked with other selects using union all.

Who is to say what you are building is not also clumsy, maybe even more so (I do not mean this as a negative comment to you). I suggest that the original developer weighed the same issues your are dealing with right now and determined that it was best to use UNION ALL.

1) it keeps all the code in one place so it is easy to add new parts when necessary

2) it avoids having to do dynamic sql and construct a component with multiple moving parts, both of which you are doing right now. This may just be "beauty in the eye of the beholder" because you think what you are doing is better and the guy before you thought not. My point is that you assume the original developer did not consider alternatives to the multi-part cursor when in fact he likely did and landed on this as the best solution. It would suggest that the original developer is likely an experienced Oracle guy because I would do what he has done.

If your queries are indeed very similar, and you are adamant about "combining parts" in order to make it look "better" then maybe you should have a look at the WITH clause. You can possibly reduce the amount of code in the statement as a whole by combining some or all of the common code into a WITH expression that you can then reference multiple times.

But my advice is the same as the prior repondent, stick with the one query and one cursor. I think it is cleaner and will be much easier to maintain later.

Do not knock yourself though. It is good you are looking to refine code. It means you are thinking about more that just what it does, but also how it does it.

Good luck, Kevin

[Updated on: Thu, 25 June 2009 22:29]

Report message to a moderator

Re: Wrapping multiple cursors resultset into one? (merged 2) [message #410315 is a reply to message #410261] Fri, 26 June 2009 02:55 Go to previous messageGo to next message
h0003192
Messages: 4
Registered: June 2009
Location: UK
Junior Member
Hi all,

Thanks for the feedback, I will post the SQL statement shortly to give you a clearer background.

For the refinement, in fact, the original developer has been trying to modularize the code before I took part and the fact that its the way it is with the UNION ALL is because he didnt know how to call the procedures within a procedure (yes, both he and I are relatively novice Oracle user) and not because he thought of the multi-cursor first and then dumped that.

And also, the reason we are trying to use procedure within procedure to build the dynamic SQL is because the three UNION ALL SQL queries are exactly the same but just one table difference. And the tables they refered to are same in schema.

That's why we are trying to just write one dynamic SQL to handle that. And yes, we agree that its just 'beauty in the eye' but not necessaily faster. But at least we can only need to maintain one dynamic SQL instead of the three (and probablly will be more) UNION all SQL statements.

Thanks
Michael
Re: Wrapping multiple cursors resultset into one? (merged 2) [message #410342 is a reply to message #410315] Fri, 26 June 2009 07:31 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, you are close to the problem than we are. I await the SQL in order to see why you want to recraft the solution.

But take note, the experienced guys who have replied so far including myself think the union all is the way to go.

Thanks for the reply, Kevin
Re: Wrapping multiple cursors resultset into one? (merged 2) [message #410355 is a reply to message #410342] Fri, 26 June 2009 08:45 Go to previous messageGo to next message
h0003192
Messages: 4
Registered: June 2009
Location: UK
Junior Member
Hi all,

Here are the codes. Apologise if this is toolong for the post.

    PROCEDURE   market (
                 p_posdate IN VARCHAR2,
                 p_posdate_m1 IN VARCHAR2,
                 r_num1 IN NUMBER DEFAULT NULL,
                 r_num2 IN NUMBER DEFAULT NULL,
                 r_num3 IN NUMBER DEFAULT NULL,
                 r_num4 IN NUMBER DEFAULT NULL,
                 Pmycursor OUT mycursor) -- Use cursor
                 IS

        r_num list_number;     --Just an array of numbers

   BEGIN

       IF r_num1 IS NULL OR r_num1 = 0 THEN

           SELECT   rer.report_number BULK COLLECT INTO r_num
           FROM     risk.report_extract_run@GE RER
           WHERE    rer.eod_report='Y'
           AND      rer.horizon= 'L'
           AND      rer.eod_position_date in (p_posdate , p_posdate_m1)
           AND      rer.cash_at_risk <> 'Y';

        ELSE

            r_num := list_number();
            r_num.EXTEND(4);
            r_num(1) := r_num1;
            r_num(2) := r_num2;
            r_num(3) := r_num3;
            r_num(4) := r_num4;

        END IF;

        OPEN Pmycursor FOR

        SELECT  a.forward_curve_name,
                a.curve_point_code,
                a.commodity,
                MAX(a.extract_type) AS extract_type,
                to_char(a.curve_point_del_start,'DD-MON-YYYY') AS start_date,
                to_char(a.curve_point_del_end,'DD-MON-YYYY') AS end_date,
                a.price_dependency,
                CASE WHEN a.price_dependency IS NOT NULL THEN
                    substr(a.forward_curve_name,1,instr(a.forward_curve_name,'.',-1)-1)
                ELSE
                    NULL
                END AS FC_GROUP,
                a.delivery_time_code,
                a.week_ahead
                FROM
                (
                 SELECT  ext.forward_curve_name,
                         ext.curve_point_code,
                         fc.commodity,
                         1 AS extract_type,
                         ext.curve_point_del_start,
                         ext.curve_point_del_end,
                         fc.price_dependency,
                         fc.delivery_time_code,
                         weekAhead (ext.risk_factor,p_posdate) as week_ahead
                 FROM    risk.report_extracts_linear@GE ext
                 JOIN    risk.ref_profit_centre pc
                 ON      ext.ldoffice=pc.book
                 JOIN    risk.ref_forward_curve fc
                 ON      fc.name=ext.forward_curve_name
                 WHERE   ext.report_number in (SELECT * FROM TABLE(r_num))
                 AND     pc.status in ('Active')

                 UNION ALL

                 SELECT  ext.forward_curve_name,
                         ext.curve_point_code,
                         fc.commodity,
                         1,
                         ext.curve_point_del_start,
                         ext.curve_point_del_end,
                         fc.price_dependency,
                         fc.delivery_time_code,
                         weekAhead (ext.risk_factor,p_posdate)
                 FROM    risk.report_extracts_complex@GE ext
                 JOIN    risk.ref_profit_centre pc
                 ON      ext.ldoffice=pc.book
                 JOIN    risk.ref_forward_curve fc
                 ON      fc.name=ext.forward_curve_name
                 WHERE   ext.report_number in (SELECT * FROM TABLE(r_num))
                 AND     pc.status in ('Active')

                 UNION ALL

                 SELECT  ext.forward_curve_name,
                         RTRIM(ext.curve_point_id),
                         fc.commodity,
                         0,
                         ext.curve_point_start_date,
                         ext.curve_point_end_date,
                         fc.price_dependency,
                         fc.delivery_time_code,
                         weekAhead (ext.risk_factor,p_posdate)
                 FROM    risk.report_extracts_vanilla@GE ext
                 JOIN    risk.ref_profit_centre pc
                 ON      ext.ldoffice=pc.book
                 JOIN    risk.ref_forward_curve fc
                 ON      fc.name=ext.forward_curve_name
                 WHERE   ext.report_number in (SELECT * FROM TABLE(r_num))
                 AND     pc.status in ('Active')
                ) a
        GROUP BY
                a.forward_curve_name,
                a.curve_point_code,
                a.commodity,
                a.curve_point_del_start,
                a.curve_point_del_end,
                a.price_dependency,
                a.delivery_time_code,
                a.week_ahead;
    END market;




As you can see, the three SELECT queries are very similiar and we will foresee there will be more SELECT queries appending to the UNION ALL. (the no. of SELECT queries depend on the number of the 'ext' tables, which are increasing). That's why we are thinking to modularize the SELECT query into a dynamic SQL procedure with the 'ext' table as one of the parameters. This way, we can maintain and scale the code much easily.

As said, we are quite novice in Oracle so really thanks in advance for any feedback!
Michael
Re: Wrapping multiple cursors resultset into one? (merged 2) [message #410359 is a reply to message #410198] Fri, 26 June 2009 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want maintainable and scalable code avoid dynamic sql like the plague. It's horribly slow and if you're going to add more tables that deficiency is only going to become more obvious.

You've already got an inline view. Just move the stuff that's the same in each select out of the inline view and into the outer part of the select.

If the inline view is still bugging you then replace it with a real view.
Re: Wrapping multiple cursors resultset into one? (merged 2) [message #410364 is a reply to message #410359] Fri, 26 June 2009 09:29 Go to previous messageGo to next message
h0003192
Messages: 4
Registered: June 2009
Location: UK
Junior Member
Hi,

Thanks for the feedback. Can you tell me more about how to move the repetitive stuff out? After all, the three select statements are not exactly the same, the 'ext' tables are different. So not sure how to replace all three SELECT statements into inline view.

thanks
Michael
Re: Wrapping multiple cursors resultset into one? (merged 2) [message #410368 is a reply to message #410364] Fri, 26 June 2009 09:58 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Sorry, but after looking at this query, I like it.

If it was me, I would not change. It looks well written to me. If follows an easily decernable pattern and each query is clearly looking at something different.

Your problem is complicated by the fact that the table which is changing is in fact the middle table in your join sequence so trying to remove it is problematic.

Again, I suggest you rethink your decision that this sql is somehow bad. I think it is pretty good.

Still, how about trying something like this. You will notice there is no getting around the UNION ALL because of the three different tables. Indeed, you still have not figured out how to do it with your dynamic sql and "combine" results of your different cursors. The difficulty of combining rowsets into a single final result without UNION ALL is one of the reasons that you should not be going the route you are heading. I think you will realize this when you try to finish your multi-cursory dynamic solution. For that reason it is a good idea to continue what you are doing, because you will learn something from it.

The query below is smaller than your original, and it exposes the union all more readily as a facet of the middle table that changes so that goes towards your modularity requirement because it isolates this aspect of the code.

The WITH clause is a great feature when used correctly.

with
      report_extracts as (
                           select
                                    'Linear' rowtype
                                  , 1 extract_type
                                  , ext.forward_curve_name
                                  , ext.curve_point_code
                                  , ext.curve_point_del_start
                                  , ext.curve_point_del_end
                                  , ext.risk_factor
                                  , ext.ldoffice
                                  , ext.report_number
                           from risk.report_extracts_linear@GE ext
                           where ext.report_number in (SELECT * FROM TABLE(r_num))
                           union all
                           select
                                    'Complex' rowtype
                                  , 1 extract_type
                                  , ext.forward_curve_name
                                  , ext.curve_point_code
                                  , ext.curve_point_del_start
                                  , ext.curve_point_del_end
                                  , ext.risk_factor
                                  , ext.ldoffice
                                  , ext.report_number
                           from risk.report_extracts_complex@GE ext
                           where ext.report_number in (SELECT * FROM TABLE(r_num))
                           union all
                           select
                                    'Vanilla' rowtype
                                  , 0 extract_type
                                  , ext.forward_curve_name
                                  , RTRIM(ext.curve_point_id)
                                  , ext.curve_point_start_date
                                  , ext.curve_point_end_date
                                  , ext.risk_factor
                                  , ext.ldoffice
                                  , ext.report_number
                           from risk.report_extracts_vanilla@GE ext
                           where ext.report_number in (SELECT * FROM TABLE(r_num))
                         )
        SELECT  ext.forward_curve_name,
                ext.curve_point_code,
                ext.commodity,
                MAX(ext.extract_type) AS extract_type,
                to_char(ext.curve_point_del_start,'DD-MON-YYYY') AS start_date,
                to_char(ext.curve_point_del_end,'DD-MON-YYYY') AS end_date,
                fc.price_dependency,
                CASE WHEN fc.price_dependency IS NOT NULL THEN
                    substr(ext.forward_curve_name,1,instr(ext.forward_curve_name,'.',-1)-1)
                ELSE
                    NULL
                END AS FC_GROUP,
                fc.delivery_time_code,
                weekAhead (ext.risk_factor,p_posdate) week_ahead
        FROM    report_extracts ext
        JOIN    risk.ref_profit_centre pc
        ON      ext.ldoffice=pc.book
        JOIN    risk.ref_forward_curve fc
        ON      fc.name=ext.forward_curve_name
        WHERE   pc.status in ('Active')
        GROUP BY
                ext.forward_curve_name,
                ext.curve_point_code,
                ext.commodity,
                ext.curve_point_del_start,
                ext.curve_point_del_end,
                fc.price_dependency,
                fc.delivery_time_code,
                weekAhead (ext.risk_factor,p_posdate);


Kevin

[Updated on: Fri, 26 June 2009 10:04]

Report message to a moderator

Previous Topic: Data display from table
Next Topic: file creation
Goto Forum:
  


Current Time: Sat Dec 10 16:29:18 CST 2016

Total time taken to generate the page: 0.20510 seconds