SQL & PL/SQL

Hi,

I am trying to build a schema compare script which will compare and alert on changes on a single schema. I have a batch job which inserts all objects from the DBA_ views into my custom compare tables. I am having trouble with the INDEXED COLUMNS compare SQL.

My compare table (T_COMP_IND_COLS) looks like the following. Batch ID 1 was run yesterday when CUST_NAME_I index only had one column. Batch ID 2 was run today and now CUST_NAME_I has 2 columns.

I am trying to write a query on T_COMP_IND_COLS that will allow me to show differences including the index name and the new columns added (or deleted).

I have tried the below SQL but this is not returning the data the way I want.

select t1.owner as own1, t1.table_name as Tbl1, t1.index_name as Ind1, t1.column_name as col1, t1.column_position as pos1, t1.batch_id,

       t2.owner as own2, t2.table_name as Tbl2, t2.index_name as Ind2, t2.column_name as col2, t2.column_position as pos2, t2.batch_id

from t_cmp_ind_col t1, t_cmp_ind_col t2

     where t1.batch_id = 2

     and t2.batch_id =  1

     and t1.owner = t2.owner

     and t1.index_name = t2.index_name

     and t1.column_name != t2.column_name

order by ind2,pos2;

Does anybody know of what logic I cna use here or if there are any SQL functions that I can use to show only the index that has changed and the old and new column structures (eg Batch ID 1 index structure and Batch ID 2 index structure)? Any help would be greatly appreciated.

Thanks.

Rob