Home » SQL & PL/SQL » SQL & PL/SQL » compare TWO CLOB column in two table (merged) (oracle 9 , linux)
compare TWO CLOB column in two table (merged) [message #436643] Thu, 24 December 2009 20:42 Go to next message
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 #436646 is a reply to message #436643] Thu, 24 December 2009 20:55 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>If I have two table having identical column
A prime example is poor design which violates Third Normal Form.

(SELECT * FROM TABLE1
MINUS
SELECT * FROM TABLE2)
UNION
(SELECT * FROM TABLE2
MINUS
SELECT * FROM TABLE1)
Re: compare two table [message #436648 is a reply to message #436646] Thu, 24 December 2009 21:07 Go to previous messageGo to next message
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 #436651 is a reply to message #436648] Thu, 24 December 2009 21:20 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
I am not surprised.
I suspected that the CLOBs would be a problem.

If the content of the CLOBs need to be compared, you'll need to code up your own PL/SQL to do so.

If the CLOBs can be ignored, just list all the non-CLOB columns.
Re: compare two table [message #436654 is a reply to message #436651] Thu, 24 December 2009 21:35 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
I do not know much about CLOB ,
Any advice on this ??/
Re: compare two table [message #436655 is a reply to message #436654] Thu, 24 December 2009 21:37 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>Any advice on this ??
I don't know what YOUR requirements are.
Re: compare two table [message #436656 is a reply to message #436655] Thu, 24 December 2009 21:52 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
All the column in the table is need to compare include CLOB.
So, How I can compare these two table ???
Any advice ??
Re: compare two table [message #436657 is a reply to message #436656] Thu, 24 December 2009 22:00 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
chuikingman wrote on Thu, 24 December 2009 19:52
All 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

Re: compare two table [message #436658 is a reply to message #436657] Thu, 24 December 2009 22:12 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
I do not understand .
How to "Loop throught" ???
Please describe more detailed....
Re: compare two table [message #436659 is a reply to message #436658] Thu, 24 December 2009 22:18 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/?SQ=03495a9a121079bea36c5e281ae67629&t=search&srch=LOOP&btn_submit=Search&field=all&f orum_limiter=1&search_logic=AND&sort_order=DESC&author=JRowbottom

PL/SQL has a variety of ways to LOOP

You get to pick which is best for this case.
compare TWO CLOB column in two table [message #436661 is a reply to message #436643] Thu, 24 December 2009 23:03 Go to previous messageGo to next message
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)

Re: compare TWO CLOB column in two table [message #436662 is a reply to message #436661] Thu, 24 December 2009 23:18 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#sthref2988
Re: compare TWO CLOB column in two table [message #436663 is a reply to message #436662] Thu, 24 December 2009 23:21 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
Any example instead of the syntax ???
I think the exampe is more easy to understand .....
Re: compare TWO CLOB column in two table [message #436664 is a reply to message #436663] Thu, 24 December 2009 23:23 Go to previous message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
I am sorry to see that GOOGLE & search are broken for you.
Please be patient while repairs are completed.
Additional information will be posted when it becomes available.
Previous Topic: A table may be outer join?
Next Topic: select last value without analytic function
Goto Forum:
  


Current Time: Fri Dec 09 09:34:51 CST 2016

Total time taken to generate the page: 0.15212 seconds