Re: Diff between the results returned by two ref cursors
- From: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
- To: srinivas.chintamani@xxxxxxxxx
- Date: Wed, 6 Jan 2010 14:50:49 +1100
How about writing two pipelined functions - one for each ref cursor.
Then it's a simple matter of :
select * from table(pipe_func_1)
minus
select * from table(pipe_func_2)
Hope this helps.
Steve
On Wed, Jan 6, 2010 at 2:43 PM, Srinivas Chintamani
<srinivas.chintamani@xxxxxxxxx> wrote:
> Hi Listers,
> I have a rather strange requirement to diff the results returned by two
> different REF CURSORS.
> The background is something like this - There is an existing procedure say X
> that returns a ref cursor (R1) in an out variable. This procedure code has
> been majorly refactored into a new procedure say Y which returns yet another
> ref cursor (R2). What I am being asked to do is programatically diff the
> results from these cursors R1 and R2. Is it possible to do that?
> Here is the basic idea of the code. Of course, I simplified the posted code
> to clarify what I need to get done.
> I have tried bulk collecting into PLSQL nested tables from each of these
> procedure returned ref cursors, but further am not able to do a MINUS
> between the result sets. I will really appreciate any pointers towards
> helping me solve this.
> ------ Code Start -----------
> create or replace package my_pkg as
> type my_rec is record
> (x integer,
> y integer
> );
> type my_cur is ref cursor return my_rec;
> procedure orig_proc (i_param_one in integer, o_cur_orig out my_cur);
> procedure refactored_proc (i_param_one in integer, o_cur_refactored out
> my_cur);
> end my_pkg;
> /
> create or replace package body my_pkg as
> procedure orig_proc (i_param_one in integer, o_cur_orig out my_cur) is
> begin
> open o_cur_orig
> for select 1, 2 from dual;
> end orig_proc;
> procedure refactored_proc (i_param_one in integer, o_cur_refactored out
> my_cur) is
> begin
> open o_cur_refactored
> for select 3, 4 from dual;
> end refactored_proc;
> end my_pkg;
> /
>
> ------ Code End ------------
> --
> Regards,
> Srinivas.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
