Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 10: PL SQL: Comparing records from two (ref) cursors
Oracle 10: PL SQL: Comparing records from two (ref) cursors [message #260091] Fri, 17 August 2007 07:14 Go to next message
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 #260097 is a reply to message #260091] Fri, 17 August 2007 07:32 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

You can convert the data from row to column.
For this you have to use an user defind function.
For the function details see the link Rows to Column
After that you can compare it using a single cursor.


Cheers
Sanka
Re: Oracle 10: PL SQL: Comparing records from two (ref) cursors [message #260099 is a reply to message #260097] Fri, 17 August 2007 07:44 Go to previous messageGo to next message
QuestionMaster
Messages: 3
Registered: August 2007
Location: Earth
Junior Member
But since I have to records in two cursors (as results from different functions with dynamic sql inside) how to get them back into just one?

Also how will it make comparing easier, when the columns are turned into rows?

Thanks for the quick reply.

[Updated on: Fri, 17 August 2007 07:45]

Report message to a moderator

Re: Oracle 10: PL SQL: Comparing records from two (ref) cursors [message #260436 is a reply to message #260099] Sun, 19 August 2007 21:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could load the results of the two ref cursors into Global Temporary Tables and then compare them with INTERSECT / MINUS set operators.

Ross Leishman
Re: Oracle 10: PL SQL: Comparing records from two (ref) cursors [message #260638 is a reply to message #260436] Mon, 20 August 2007 09:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could write a function to do a column by column equality check on two records of that type.
It would be a bit time consuming to write, but once you've got it you can do the check easily.
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 Go to previous messageGo to next message
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!

icon5.gif  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 Go to previous message
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


Previous Topic: SQL for check the integrity of 2 database.
Next Topic: ORA-12805: parallel query server died unexpectedly
Goto Forum:
  


Current Time: Sat Oct 01 17:51:42 CDT 2016

Total time taken to generate the page: 0.11342 seconds