Diff between the results returned by two ref cursors
- From: Srinivas Chintamani <srinivas.chintamani@xxxxxxxxx>
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 5 Jan 2010 22:43:28 -0500
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.
Other related posts:
