Re: Comparing Tables

From: The Magnet <art_at_unsu.com>
Date: Sat, 22 Sep 2012 12:26:23 -0700 (PDT)
Message-ID: <df8aa75d-1230-46d2-b61b-6c68831f13e8_at_e18g2000yqo.googlegroups.com>

On Sep 22, 1:15 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "The Magnet" <a..._at_unsu.com> wrote in message
>
> news:cb932f27-ed7f-4e5c-97fa-afd9327e9a15_at_n9g2000yqn.googlegroups.com...
> |
> | Hi,
> |
> | I am using this SQL to perform some simple table compares:
> |
> | data_rec=`sqlplus -s $user/${password} << "EOF"
> |  set heading off feedback off verify off timing off
> |  SELECT COUNT(*) FROM
> |     (SELECT * FROM master_table MINUS SELECT * FROM
> | master_table_at_dblink);
> |  exit
> | EOF`
> |
> |
> | However, one of the table a complex data type, with nested tables.  I
> | cannot use the above method on nested tables.  Does anyone know a good
> | set of code to compare 2 identical tables, that contain nested tables?
> |
> | Thanks!
>
> Your SQL is probably deficient. If you want to find differences between the
> tables you need to do something symmetrical like:
>
> select
> from
> (select * from table1 minus select * from table2)
> union all
> (select * from table2 minus select * from table1)
>
> You could write your code to take this approach for tables where
> user_tab_columns shows no "illegal" columns such as LOBs, longs, nested
> tables etc.
> For the remaining tables you could look at the package dbms_rectifier_diff
> (which exists to sort out replication errors) from dbmsrctf.sql. This MIGHT
> cover all the options you want but, as you might guess, it's likely to be
> slow because it has to do row by row comparison.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com/all_postings
>
> Author: Oracle Core (Apress 2011)http://www.apress.com/9781430239543

Oracle really needs to come up with some decent utilities. Even to this day, every other language has a SPLIT function, except PL/SQL. If 2 tables are identical in structure, then I should be able to compare them. The fact these these tables also have nested tables, Oracle should be able to deal with that easily.

The MD5 idea is the only one I can think of too. Although, being able to compare each nested table with the same nested table in the other table would help identify where the differences are. Received on Sat Sep 22 2012 - 14:26:23 CDT