asked on

Compare Contents of Two Oracle Tables Containing a CLOB Data Type

I am trying to compare the data contents of two tables to make sure they match.

In performing the following select, I received error:
ORA-00932: inconsistent datatypes: expected - got CLOB

select * from table1
minus
select * from table2;

How can I get around this?

ASKER CERTIFIED SOLUTION

ppinon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Julie Kurpa

ASKER

Wow...you must have been sitting right on your keyboard.

I've got some questions for you ppinon:
My tables are identically built (same column names..just different owners) and there are several columns.

I need to compare all the data of table1 (all columns) to all the data of table2 (all columns). They are not large tables, however I must repeat this process for 200 other tables.

If I use this dbms_log.substr() technique, it appears I'll need to list each column in my query.
Is there a more efficient way of performing this query when a table contains a CLOB data type?

ppinon

Well if you want to automate such comparison, I suggest to write a PL/SQL block comparing each column.

An example (I've not tested, but the idea is here):

CREATE OR REPLACE PROCEDURE compare_tables(i_table1 VARCHAR2, i_table2 VARCHAR2) IS
v_isequal CHAR(1) := 'Y';
v_tmp PLS_INTEGER := 1;
v_stmt VARCHAR2(1000);
BEGIN
FOR r in (SELECT column_name FROM user_tab_columns WHERE table_name = 'table1') LOOP
v_stmt := 'SELECT 0 FROM DUAL WHERE EXISTS (SELECT '||dbms_lob.substr(r.column_name)||' FROM '||i_table1||' MINUS SELECT '||dbms_lob.substr(r.column_name)||' FROM '||i_table2||')';
EXECUTE IMMEDIATE v_stmt INTO v_tmp;
IF v_tmp = 0 THEN
v_isequal := 'N';
END IF;
END LOOP;
dbms_output.put_line('Result is:'||v_isequal );
END;
/

ppinon

Of course when you have tested such procedure to compare 2 tables, you can call the function (just change the procedure in a function and return the result) in a loop, like:

DECLARE
v_result CHAR(1);
BEGIN
FOR r IN (SELECT table_name FROM user_tables WHERE your conditions) LOOP
v_result := compare_tables(r.x,r.y);
END LOOP;
... and log the differences
END;
/

Julie Kurpa

ASKER

Am working on your procedure. I need to clarify some things and apologize for leaving them out. I didn't realize it would be so complex to compare two tables with a CLOB column.

There are two schemas: Schema1 and Schema2
They each have the exact same set of tables...same names, same structures.
Schema1's tables were populated from one source and schema2's tables were populated from another source. I'm comparing the two to make sure the process used to populate them is working properly.

It is possible to change the names of the tables for one of the schemas if that is required to be able to compare them. It's just a bit of an effort to change all the other processes that has let up to this compare step.

ppinon

Yes you can change the names of the table using:

ALTER TABLE table_name RENAME TO new_table_name;

and you you want to automate for 200 tables in a schema:

BEGIN
FOR r IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||r.table_name||' RENAME TO '||r.table_name||'_new';
END LOOP;
END;
/

After executing this, for instance the table 'TABLE1' will become 'TABLE1_new'

Julie Kurpa

ASKER

To avoid changing the names of the tables, I went ahead and created a procedure to read all the tables and build a compare script. It tests to see if a column is CLOB and then modifies the SELECT..MINUS accordingly.

It seems to work just fine. The only thing is the SELECT...MINUS command only returns (if there is a difference) the column name and value that is different. It doesn't identify the table itself. So if I've got the SELECT...MINUS for all columns for all tables stacked into one script, it's very difficult to tell which table had the discrepancy.

Knowing that I will be comparing 200 tables, I'd like to avoid spooling each SELECT...MINUS statement to an individual spool file (I don't want to have to search through 200 files to find discrepancies).

Is there a way to have the SELECT...MINUS return the table name along with the column name in the spooled output whenever a discrepancy is found?

                              as                         
v_output_file1 utl_file.file_type;            
fHandle utl_file.file_type;                            
vTextOut varchar2(25000);                            
text varchar2(25000);                             
v_lasttablename varchar2(30);                            
v_lastcolumnname varchar2(30);             
v_lastdatatype varchar2(10);                   
                       
CURSOR c_tlist IS                             
 select owner,lower(table_name) as table_name,column_name,data_type                           
 from dba_tab_columns                            
 where owner='OWNER1'                                   
 order by table_name,column_id;                            
                             
v_tlist c_tlist%ROWTYPE;                      
               
BEGIN                             
                             
 OPEN c_tlist;                             
 FETCH c_tlist INTO v_tlist;                             
                         
 v_lasttablename := v_tlist.table_name;                            
 v_lastcolumnname := v_tlist.column_name;              
 v_lastdatatype := v_tlist.data_type;                  
                              
 v_output_file1 := UTL_FILE.FOPEN('REPL_DIR','compare_tables.sql','W');             
 utl_file.put_line(v_output_file1,'spool compare_tables.spo;');                  
                             
FETCH c_tlist INTO v_tlist;                             
                            
WHILE c_tlist%FOUND LOOP                             
                            
 IF v_tlist.table_name LIKE v_lasttablename THEN             
   IF v_lastdatatype LIKE 'CLOB' THEN         
     utl_file.put_line(v_output_file1,'SELECT dbms_lob.substr('||v_lastcolumnname||') FROM OWNER1.'||v_lasttablename);                                  
     utl_file.put_line(v_output_file1,'MINUS');                             
     utl_file.put_line(v_output_file1,'SELECT dbms_lob.substr('||v_lastcolumnname||') FROM OWNER2.'||v_lasttablename||';');            
     utl_file.put_line(v_output_file1,' ');                             
   ELSE         
     utl_file.put_line(v_output_file1,'SELECT '||v_lastcolumnname||' FROM OWNER1.'||v_lasttablename);                                  
     utl_file.put_line(v_output_file1,'MINUS');                             
     utl_file.put_line(v_output_file1,'SELECT '||v_lastcolumnname||' FROM OWNER2.'||v_lasttablename||';');               
     utl_file.put_line(v_output_file1,' ');                             
   END IF;         
   v_lasttablename := v_tlist.table_name;                            
   v_lastcolumnname := v_tlist.column_name;           
   v_lastdatatype := v_tlist.data_type;                      
 ELSE               
   v_lasttablename := v_tlist.table_name;                            
   v_lastcolumnname := v_tlist.column_name;          
   v_lastdatatype := v_tlist.data_type;                       
 END IF;                            
                            
  FETCH c_tlist INTO v_tlist;                             
                            
 END LOOP;                            
     utl_file.put_line(v_output_file1,'SELECT '||v_lastcolumnname||' FROM OWNER1.'||v_lasttablename);                                  
     utl_file.put_line(v_output_file1,'MINUS');                             
     utl_file.put_line(v_output_file1,'SELECT '||v_lastcolumnname||' FROM OWNER2.'||v_lasttablename||';');               
     utl_file.put_line(v_output_file1,' ');                             
                    
 utl_file.put_line(v_output_file1,'spool off;');                                  
            
 utl_file.fclose_all;                             
                             
fHandle := UTL_FILE.FOPEN('REPL_DIR','compare_tables.sql','r');                             
                             
IF UTL_FILE.IS_OPEN(fHandle) THEN                            
DBMS_OUTPUT.PUT_LINE('File read open');                             
ELSE                             
DBMS_OUTPUT.PUT_LINE('File read not open');                            
END IF;                             
                             
 loop                             
 begin                             
  UTL_FILE.GET_LINE(fHandle,vTextOut);                            
 EXCEPTION                             
 WHEN NO_DATA_FOUND THEN EXIT;                            
 end;                            
 END LOOP;                            
                            
 UTL_FILE.FCLOSE(fHandle);                             
                            
                            
END;

Open in new window

Julie Kurpa

ASKER

I ended up adding a line to print the name of the table and column each time it prints the results of the compare.
It probably isn't the most efficient but it works.
Thank for the code.

Julie Kurpa

ASKER

Gave me a basic solution but didn't help me further when asked. Felt like I got dropped.