| compare TWO CLOB column in two table (merged) [message #436643] |
Thu, 24 December 2009 20:42  |
chuikingman
Messages: 90
Registered: August 2009
|
Member |
|
|
Hi,
If I have two table having identical column ,
how can I compare and find out whether there is any different in the field by SQL command ???
ANy advice ??
Belows is the column of the table :
SQL> desc ckm1
Name Null? Type
----------------------------------------- -------- ----------------------------
N_COMP_ID NOT NULL NUMBER(10)
C_COMP_NAME NOT NULL VARCHAR2(120)
C_HOSTNAME VARCHAR2(20)
N_FRWK_COMP_FLAG NOT NULL NUMBER(1)
C_COMP_ARGS CLOB
C_COMP_ENV VARCHAR2(1024)
|
|
|
|
|
|
| Re: compare two table [message #436648 is a reply to message #436646] |
Thu, 24 December 2009 21:07  |
chuikingman
Messages: 90
Registered: August 2009
|
Member |
|
|
Hi,
I try but it is not work ,
Any advice .
I copy the description of column in these two table ckm1 , ckm2 .
SQL> (SELECT * FROM ckm1
MINUS
SELECT * FROM ckm2)
UNION
(SELECT * FROM ckm2
MINUS
SELECT * FROM ckm1) 2 3 4 5 6 7 ;
(SELECT * FROM ckm1
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
SQL> desc ckm1
Name Null? Type
----------------------------------------- -------- ----------------------------
N_COMP_ID NOT NULL NUMBER(10)
C_COMP_NAME NOT NULL VARCHAR2(120)
C_HOSTNAME VARCHAR2(20)
N_FRWK_COMP_FLAG NOT NULL NUMBER(1)
C_COMP_ARGS CLOB
C_COMP_ENV VARCHAR2(1024)
SQL> desc ckm2
Name Null? Type
----------------------------------------- -------- ----------------------------
N_COMP_ID NOT NULL NUMBER(10)
C_COMP_NAME NOT NULL VARCHAR2(120)
C_HOSTNAME VARCHAR2(20)
N_FRWK_COMP_FLAG NOT NULL NUMBER(1)
C_COMP_ARGS CLOB
C_COMP_ENV VARCHAR2(1024)
|
|
|
|
|
|
|
|
|
|
|
|
| Re: compare two table [message #436657 is a reply to message #436656] |
Thu, 24 December 2009 22:00 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
chuikingman wrote on Thu, 24 December 2009 19:52All the column in the table is need to compare include CLOB.
So, How I can compare these two table ???
Any advice ??
If the content of the CLOBs need to be compared, you'll need to code up your own PL/SQL to do so.
You need to LOOP thru every record in both tables.
I believe 1 of 4 conditions will occur.
1) Both records match in every field.
2) A record exists in TABLE1 but not in TABLE2
3) A record exists in TABLE2 but not in TABLE1
4) N_COMP_ID match in both tables, but 1 or more of other fields do not match.
You'll have to LOOP through the CLOB & compare 1 chunk at a time.
Is the goal to find matches or differences?
[Updated on: Thu, 24 December 2009 22:04] Report message to a moderator |
|
|
|
|
|
|
|
| compare TWO CLOB column in two table [message #436661 is a reply to message #436643] |
Thu, 24 December 2009 23:03 |
chuikingman
Messages: 90
Registered: August 2009
|
Member |
|
|
Hi,
I have two table with identical column.
There is CLOB column,
How I can compare two CLOB column are identical or different in two table ??
Any advice
Below is the column of table
SQL> desc ckm1
Name Null? Type
----------------------------------------- -------- ----------------------------
N_COMP_ID NOT NULL NUMBER(10)
C_COMP_NAME NOT NULL VARCHAR2(120)
C_HOSTNAME VARCHAR2(20)
N_FRWK_COMP_FLAG NOT NULL NUMBER(1)
C_COMP_ARGS CLOB
C_COMP_ENV VARCHAR2(1024)
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
