?
Solved

Compare Contents of Two Oracle Tables Containing a CLOB Data Type

Posted on 2009-12-22
9
Medium Priority
?
1,095 Views
Last Modified: 2013-12-18
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?
0
Comment
Question by:vocogov
  • 5
  • 4
9 Comments
 
LVL 7

Accepted Solution

by:
ppinon earned 500 total points
ID: 26104825
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
 
LVL 1

Author Comment

by:vocogov
ID: 26104957
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
 
LVL 7

Expert Comment

by:ppinon
ID: 26105096
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 7

Expert Comment

by:ppinon
ID: 26105159
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
 
LVL 1

Author Comment

by:vocogov
ID: 26106868
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
 
LVL 7

Expert Comment

by:ppinon
ID: 26130477
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
 
LVL 1

Author Comment

by:vocogov
ID: 26133219
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
 
LVL 1

Author Comment

by:vocogov
ID: 26202250
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
 
LVL 1

Author Closing Comment

by:vocogov
ID: 31668982
Gave me a basic solution but didn't help me further when asked.  Felt like I got dropped.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question