| Oracle 10: PL SQL: Comparing records from two (ref) cursors [message #260091] |
Fri, 17 August 2007 07:14  |
QuestionMaster
Messages: 3
Registered: August 2007
Location: Earth
|
Junior Member |
|
|
In my PL SQL script, after running some functions with dynaminc sql and calculations...
I have two cursors (ref cursors) that both have many columns (hundreds) but only one record inside. I want to compare the records from those two cursors (the structure and column names etc. are the same).
How can this be done? Othere than comparing all the columns one by one in one gigantic if statement.
More details:
I've two ref cursors (both only containing one record with simulair structure and column names etc. but possibly different results):
- Cursor_Old_Calculations
- Cursor_New_Calculations
I've to check if the column results in the records are the same, and only do an update if the results are different.
So basicly I want to do a:
If {Cursor_Old_Calculations = Cursor_New_Calculations}
THEN {Update, update count + 1.}
ELSE {Already okay.}
END IF;
Maybe someone here ran into this before. Anyways, any help is much appriciated. Thx!
[Updated on: Fri, 17 August 2007 07:42] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle 10: PL SQL: Comparing records from two (ref) cursors [message #260742 is a reply to message #260091] |
Mon, 20 August 2007 17:46  |
sumiche
Messages: 5
Registered: October 2006
Location: New York, NY
|
Junior Member |
|
|
Use the Oracle meta-data table "COL" ... do a describe on it so that you see the columns.
(I am writing this all quickly and leave it to you to punctuate and format it correctly ... the parts of my own code where I use this are way too complex to pare down .. use if or case statements as you like.)
declare
my_cur is "Select cname from col where TNAME in (your table names) order by tname, colno"
Query1 varchar2(1000) := '';
--(long enuf to hold names of all columns at once
Query2 varchar2(1000) := '';
--(long enuf to hold names of all columns at once
Result1 varchar2(1000) := '';
--(long enuf to hold VALUES of all columns at once
Result2 varchar2(1000) := '';
--(long enuf to hold VALUES of all columns at once
begin
for my_rec in my_cur loop
-- the '^' char should be something that does NOT occur
-- naturally in you data, e.g., use ^^ or ^^^ if one caret
-- alone CAN occur. Tes, I intend to embed || in strings.
if TNAME = table1 then
Query1 := (case when Query1 = '' then my_rec.cname
else Query1||'||^||'||my_rec.cname end);
end if;
if TNAME = table2 then
Query2 := (case when Query2 = '' then my_rec.cname
else Query2||'||^||'||my_rec.cname end);
end if;
end loop;
Query1 := 'Select '||Query1||' from Table1'||
' where <selection criteria if necessary>';
Query2 := 'Select '||Query2||' from Table2'||
' where <selection criteria if necessary>';
Open RefCursor1 for Query1;
Fetch Refcursor1 into Result1;
(all those embedded || will drop out, concatenating the result)
(but if you don't embed your own separator, you will make it way
hard to debug the output comparison for humans, while you are testing)
Open RefCursor2 for Query2;
Fetch Refcursor2 into Result2;
(if each table only has one record, no need for selection criteria above)
if Result1 <> Result2 then
-- do whatever
end if.
Close your cursors, etc.
(I assume you would check for found/notfound, etc.)
------------------
Something along lines of above should work.
We use this to export unknown table structures at run time, and for other purposes (code that writes code that writes code ...)
ANOTHER way to do it is to throw these things into an array structure and then compare the two lines of the array (or PL/SQL table or similar structure).
Good luck!
|
|
|
|
 Re: Oracle 10: PL SQL: Comparing records from two (ref) cursors [message #420541 is a reply to message #260742] |
Mon, 31 August 2009 04:20 |
Assaf
Messages: 2
Registered: August 2008
|
Junior Member |
|
|
Hi folks
I would like something more generic for comparing the result sets of two cursors. I would use this for testing a new version of an existing query.
ie I have re-written Old_Proc as a faster New_Proc. Both procs return a REF_CURSOR, and I want to check that the results are identical for both the old and the new versions of code.
I intend to re-use this code if possible with other procs/cursors that I am re-writing.
Run Old_Proc (returns REF_CURSOR_old);
Run New_Proc (returns REF_CURSOR2_new);
Compare the contents of REF_CURSOR_old with REF_CURSOR_new;
I am internested only in whether there are any records that do not exist in both result sets, and I would be happy with a result such as THE_CURSORS_ARE_IDENTICAL or THE_CURSORS_ARE_DIFFERENT.
I am not interested in whether they are ordered differently, and I am also not interested in identifying the specific records which are do not exist in both sets.
I imagine this has been done before, but am not managing to find advice online. Can someone suggest a good approach?
I would like to be able to do something like the following, but I can't work out if this is possible.
----------------------------------
select count(1) INTO v_count
from (
(Select * from REF_CURSOR_old
MINUS
Select * from REF_CURSOR_new)
UNION
(Select * from REF_CURSOR_new
MINUS
Select * from REF_CURSOR_old)
);
If v_count = 0 then IDENTICAL
----------------------------------
And so I'm considering things such as the following:
Option 1) Importing the results of each cursor into its own PL/SQL (index by) table and then looping through comparing them.
Option 2) Using some dunamic SQL to create tables based on the cursor, inserting their results into the tables and then performing the SQL mentioned above.
Is there a better way?
Many thanks
Assaf
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
