I have two tables with similar structure in two different schema . I want to compare tab1 records with tab2 . "Minus" operations failed to clob columns in the table.
So what is the best way to compare two tables with colb columns.
WITH tab1(sno,sname,detail_clob,prd_info)AS
(SELECT 1,'prd1',to_clob('this is test clob content1'),'invent1' FROM dual
UNION ALL
SELECT 2,'prd2',to_clob('this is test clob content2'),'invent2' FROM dual
),
tab2(sno,sname,detail_clob,prd_info)AS
(SELECT 1,'prd1',to_clob('this is test clob content1'),'invent1' FROM dual
)
SELECT * FROM tab1
MINUS
SELECT * FROM tab2;
ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 9 Column: 8
Expected output:
SNO SNAME DETAIL_CLOB PRD_INFO
2 prd2 this is test clob content2 invent2