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?
LVL 1
vocogovAsked:
Who is Participating?
 
ppinonConnect With a Mentor Commented:
You will need to use dbms_lob.substr to work on lobs. For example:
scott@Test9iEE> select * from t_lob;

LOB_STRING
--------------------------------------------------------------------------------
First String
Second String
Third String
Fourth String

Your query should be:
SELECT dbms_lob.substr(lob_string) from t_lob where rownum<5
    minus
    SELECT dbms_lob.substr(lob_string) from t_lob where rownum<2;

Output:
DBMS_LOB.SUBSTR(LOB_STRING)
--------------------------------------------------------------------------------
Fourth String
Second String
Third String
0
 
vocogovAuthor Commented:
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?
0
 
ppinonCommented:
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;
/
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ppinonCommented:
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;
/
0
 
vocogovAuthor Commented:
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.
0
 
ppinonCommented:
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'
0
 
vocogovAuthor Commented:
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

0
 
vocogovAuthor Commented:
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.
0
 
vocogovAuthor Commented:
Gave me a basic solution but didn't help me further when asked.  Felt like I got dropped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.