Oracle Database Discussions

I am working on a black-box test where I have to compare the content of two structurally equal tables before and after a certain script has run. My two tables, MDQ_OLD and MDQ_NEW, are filled with data in two separate transactions.

The two tables I am going to compare are nested, as you can see from the CREATE TABLE-scripts below.

I have tried to use the minus-operator, but without luck.

I have also tried to select the data into a type that is of %ROWTYPE of my nested tables, but that fails as well (see the script further down in this post).

Can you please help me out on this problem on how to compare the content of two nested tables?

Please run the scripts below to recreate the problem and don't hesitate to update this post if further information is required.

---Scripts below----

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

--First I create my types

CREATE OR REPLACE TYPE   MDQ_DETAIL FORCE AS OBJECT ( MDQ_DETAIL_ID NUMBER , MDQ_DETAIL_DESC VARCHAR2 (100 ));

CREATE OR REPLACE TYPE  T_MDQ_DETAIL AS TABLE OF MDQ_DETAIL ;

--Note that this type contains the table type T_MDQ_DETAIL:

CREATE OR REPLACE TYPE MDQ_PARENT FORCE AS OBJECT ( MDQ_ID NUMBER, MDQ_DETAILS T_MDQ_DETAIL );

--Then I create two equal nested tables

CREATE TABLE MDQ_OLD OF MDQ_PARENT NESTED TABLE MDQ_DETAILS STORE AS MDQ_PR_OLD ;

CREATE TABLE MDQ_NEW OF MDQ_PARENT NESTED TABLE MDQ_DETAILS STORE AS MDQ_PR_NEW ;

--Insert test data into the nested tables

Insert into MDQ_OLD(MDQ_ID, MDQ_DETAILS) Values (1, T_MDQ_DETAIL(MDQ_DETAIL(1,'desc1')));

Insert into MDQ_NEW(MDQ_ID, MDQ_DETAILS) Values (2, T_MDQ_DETAIL(MDQ_DETAIL(1,'desc1')));

--Trying to use the minus operator to compare content of the tow nested tables, but it gives this error:

--ORA-00932: inconsistent datatypes: expected - got DISPATCH.T_MDQ_DETAIL

select * from MDQ_NEW

minus

select * from MDQ_OLD;

--Trying to select into a ROWTYPE, but it fails

declare

   myTypeOld MDQ_OLD%ROWTYPE;

   myTypeNew MDQ_New%ROWTYPE;

   myTypeDiff MDQ_New%ROWTYPE;

begin

   --This select gives: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list

   select *  bulk collect into myTypeOld from mdq_old;

   select *  bulk collect into myTypeNew from mdq_new;

   --Need a  "map member function compare" on the types for multiset except to work, but as far as I

  --I am not able to bulk collect into neither myTypeOld nor myTypeNew, this doesn't help me out.

    myTypeDiff := myTypeOld multiset except myTypeNew ;

end;

--Cleanup:

drop table MDQ_OLD;

drop table MDQ_NEW;

drop type MDQ_PARENT;

drop type T_MDQ_DETAIL;

drop type MDQ_DETAIL;