OraFAQ Forum: SQL & PL/SQL » How to use minus, union for CLOB datatype column in Oracle?

Home » SQL & PL/SQL » SQL & PL/SQL » How to use minus, union for CLOB datatype column in Oracle? (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to use minus, union for CLOB datatype column in Oracle? [message #646926]

Thu, 14 January 2016 17:17

Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States

Member

Hi,

I have two tables which has similar columns with same datatypes. There is a CLOB column for holding data more than 4000 varchar.

I need to use minus, union on these tables for executing DMLs. But it is not successful, because of ORA-00932: inconsistent datatypes: expected - got CLOB. I tried using NOT EXISTS but ended in same error. What the alternate options to resolve?

Note : CLOB datatype needs to remained and data is expected to be more than 4000 VARCHAR2.

create table table_test_1 (col_1 varchar2(4000), col_2 clob);
create table table_test_2 (col_1 varchar2(4000), col_2 clob);

insert into table_test_1 values  ('1','10');
insert into table_test_1 values ('2','20');
insert into table_test_1 values ('3','30');
insert into table_test_1 values ('4','40');

insert into table_test_2 values  ('1','10');
insert into table_test_2 values ('2','20');

commit;

Case 1
---------
select col_2 from table_test_1
minus
select col_2 from table_test_2;

Case 2
---------
select col_1,col_2 from table_test_1 p1 where not exists (
select col_1,col_2 from table_test_2 p2 where p2.col_1 = p1.col_1 and p2.col_2 = p1.col_2);

Case 3
---------
select * from table_test_1
union
select * from table_test_2;

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646927 is a reply to message #646926]

Thu, 14 January 2016 17:45

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

For MINUS on CLOBs, try using dbms_lob.compare. Please see the example by Solomon Yakobson using the following link.

https://community.oracle.com/thread/1520014?tstart=0

For UNION on CLOBs, use UNION ALL instead. Please see the example and explanation by Tom Kyte using the following link.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:498299691850

[Updated on: Thu, 14 January 2016 17:47]

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646964 is a reply to message #646927]

Fri, 15 January 2016 10:12

Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States

Member

I am trying to convert example by Solomon Yakobson without WITH clause. But i could not able to proceed further, can someone help?

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646966 is a reply to message #646964]

Fri, 15 January 2016 11:40

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

SQL> select  *
  2    from  table_test_1
  3  /

COL_1      COL_2
---------- ----------
1          10
2          20
3          30
4          40

SQL> select  *
  2    from  table_test_2
  3  /

COL_1      COL_2
---------- ----------
1          10
2          20

SQL> with t1 as (
  2              select  table_test_1.*,
  3                      rownum rn
  4                from  table_test_1
  5             ),
  6       t2 as (
  7              select  rn
  8                from  t1,
  9                      table_test_2
 10                where dbms_lob.compare(t1.col_2,table_test_2.col_2) = 0
 11             ),
 12       t3 as (
 13              select  *
 14                from  t1
 15                where rn not in (select rn from t2)
 16             ),
 17       t4 as (
 18              select  col_1,
 19                      col_2,
 20                      rownum rn
 21                from  t3
 22             ),
 23       t5 as (
 24              select  t41.rn
 25                from  t4 t41,
 26                      t4 t42
 27                where dbms_lob.compare(t41.col_2,t42.col_2) = 0
 28                  and t41.rn != t42.rn
 29             )
 30  select  col_1,
 31          col_2
 32    from  t4
 33    where rn not in (select rn from t5)
 34  /

COL_1      COL_2
---------- ----------
3          30
4          40

SQL>

SY.

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646969 is a reply to message #646966]

Fri, 15 January 2016 12:16

Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States

Member

SY,

I am getting the below output for your query.

COL_1 COL_2   
3	
4

Also can you help writing this query without WITH clause? Thanks!

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646972 is a reply to message #646969]

Fri, 15 January 2016 13:26

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

You have to either use WITH clause or use in-line views. WITH clause makes it more readable.

SY.

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646973 is a reply to message #646969]

Fri, 15 January 2016 15:15

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

I don't know what you have against the WITH clause. It can be done with inline views, as shown below, but it is very hard to read and follow.

SCOTT@orcl> column col_1 format a30
SCOTT@orcl> column col_2 format a30
SCOTT@orcl> select * from table_test_1
  2  /

COL_1                          COL_2
------------------------------ ------------------------------
1                              10
2                              20
3                              30
4                              40

4 rows selected.

SCOTT@orcl> select * from table_test_2
  2  /

COL_1                          COL_2
------------------------------ ------------------------------
1                              10
2                              20

2 rows selected.

SCOTT@orcl> select  col_1, col_2
  2  from    (select  col_1, col_2, rownum rn
  3  	      from    (select  *
  4  		       from    (select	table_test_1.*, rownum rn
  5  				from	table_test_1) t1
  6  		       where   rn not in
  7  			       (select	rn
  8  				from	(select  rn
  9  					 from	 (select  table_test_1.*, rownum rn
 10  						  from	  table_test_1) t1,
 11  						 table_test_2
 12  					 where dbms_lob.compare(t1.col_2,table_test_2.col_2) = 0))))
 13  where   rn not in
 14  	     (select  rn
 15  	      from    (select  t41.rn
 16  		       from    (select	col_1, col_2, rownum rn
 17  				from	(select  *
 18  					 from	 (select  table_test_1.*, rownum rn
 19  						  from	  table_test_1) t1
 20  					 where	 rn not in
 21  						 (select  rn
 22  						  from	  (select  rn
 23  							   from    (select  table_test_1.*, rownum rn
 24  								    from    table_test_1) t1,
 25  								   table_test_2
 26  							   where   dbms_lob.compare(t1.col_2,table_test_2.col_2) = 0)))) t41,
 27  			       (select	col_1, col_2, rownum rn
 28  				from	(select  *
 29  					 from	 (select  table_test_1.*, rownum rn
 30  						  from	  table_test_1) t1
 31  					 where	 rn not in
 32  						 (select  rn
 33  						  from	  (select  rn
 34  							   from    (select  table_test_1.*, rownum rn
 35  								    from    table_test_1) t1,
 36  								   table_test_2
 37  							   where   dbms_lob.compare(t1.col_2,table_test_2.col_2) = 0)))) t42
 38  		       where	dbms_lob.compare(t41.col_2,t42.col_2) = 0
 39  		       and	t41.rn != t42.rn))
 40  /

COL_1                          COL_2
------------------------------ ------------------------------
3                              30
4                              40

2 rows selected.

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646974 is a reply to message #646969]

Fri, 15 January 2016 15:46

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Another possibility is create object type with order method. But we still need WITH clause or in-line view:

CREATE OR REPLACE
  TYPE clob_obj IS OBJECT(
                          c CLOB,
                          ORDER MEMBER FUNCTION equals(
                                                       p_c clob_obj
                                                      )
                            RETURN NUMBER 
                         )
/
CREATE OR REPLACE
  TYPE BODY clob_obj
    IS
      ORDER MEMBER FUNCTION equals(
                                   p_c clob_obj
                                  )
        RETURN NUMBER
        IS
        BEGIN
            RETURN CASE
                     WHEN SELF.c IS NULL AND p_c.c IS NULL THEN 0
                     ELSE NVL(dbms_lob.compare(SELF.c,p_c.c),1)
                   END;
      END;
END;
/
with t as (
            select  col_1,
                    clob_obj(col_2) col_2
              from  table_test_1
           minus
            select  col_1,
                    clob_obj(col_2)
              from  table_test_2
          )
select  col_1,
        t.col_2.c col_2
  from  t t
/

COL_1      COL_2
---------- ----------
3          30
4          40

select  col_1,
        t.col_2.c col_2
  from (
         select  col_1,
                 clob_obj(col_2) col_2
           from  table_test_1
        minus
         select  col_1,
                 clob_obj(col_2)
           from  table_test_2
       ) t
/


COL_1      COL_2
---------- ----------
3          30
4          40

SQL>

SY.

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646975 is a reply to message #646974]

Fri, 15 January 2016 17:17

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

I just discovered that the following works in 12c. I don't have other versions to test on.

SCOTT@orcl> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl> column col_1 format a30
SCOTT@orcl> column col_2 format a30
SCOTT@orcl> select * from table_test_1
  2  /

COL_1                          COL_2
------------------------------ ------------------------------
1                              10
2                              20
3                              30
4                              40

4 rows selected.

SCOTT@orcl> select * from table_test_2
  2  /

COL_1                          COL_2
------------------------------ ------------------------------
1                              10
2                              20

2 rows selected.

SCOTT@orcl> select  table_test_1.*
  2  from    table_test_1
  3  where   not exists
  4  	     (select  *
  5  	      from    table_test_2
  6  	      where   table_test_2.col_1 = table_test_1.col_1
  7  	      and     dbms_lob.compare (table_test_1.col_2, table_test_2.col_2) = 0)
  8  /

COL_1                          COL_2
------------------------------ ------------------------------
3                              30
4                              40

2 rows selected.

[Updated on: Fri, 15 January 2016 17:19]

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646976 is a reply to message #646975]

Fri, 15 January 2016 17:22

Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States

Member

Barbara,

I tested your recent code in Oracle Database 11g and it works pretty well. This is simple and easy to understand for me. Thank you!

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646977 is a reply to message #646976]

Fri, 15 January 2016 20:15

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

It is not equivalent to MINUS. Look what happens when table_test_1 has duplicates:

SQL> insert
  2    into  table_test_1
  3    select  *
  4      from  table_test_1
  5  /

4 rows created.

SQL> select  *
  2    from  table_test_1
  3  /

COL_1      COL_2
---------- ----------
1          10
2          20
3          30
4          40
1          10
2          20
3          30
4          40

8 rows selected.

SQL> select  table_test_1.*
  2   from    table_test_1
  3   where   not exists
  4        (select  *
  5          from    table_test_2
  6           where   table_test_2.col_1 = table_test_1.col_1
  7           and     dbms_lob.compare (table_test_1.col_2, table_test_2.col_2) = 0)
  8  /

COL_1      COL_2
---------- ----------
3          30
4          40
3          30
4          40

SQL> with t as (
  2              select  col_1,
  3                      clob_obj(col_2) col_2
  4                from  table_test_1
  5             minus
  6              select  col_1,
  7                      clob_obj(col_2)
  8                from  table_test_2
  9            )
 10  select  col_1,
 11          t.col_2.c col_2
 12    from  t t
 13  /

COL_1      COL_2
---------- ----------
3          30
4          40

SQL>

Also, MINUS (same as other SET operators) considers two NULLs equal, so query would have to use OR (table_test_2.col_X IS NULL AND table_test_1.col_X is NULL) for each column.

SY.

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646979 is a reply to message #646977]

Fri, 15 January 2016 21:25

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

Like this?

SCOTT@orcl> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl> set null null
SCOTT@orcl> column col_1 format a30
SCOTT@orcl> column col_2 format a30
SCOTT@orcl> select * from table_test_1
  2  /

COL_1                          COL_2
------------------------------ ------------------------------
1                              10
2                              20
3                              30
4                              40
6                              null
null                           60
1                              10
2                              20
3                              30
4                              40
6                              null
null                           60
5                              null
null                           50

14 rows selected.

SCOTT@orcl> select * from table_test_2
  2  /

COL_1                          COL_2
------------------------------ ------------------------------
1                              10
2                              20
5                              null
null                           50

4 rows selected.

SCOTT@orcl> select  t1.*
  2  from    table_test_1 t1
  3  where   not exists
  4  	     (select  *
  5  	      from    table_test_2 t2
  6  	      where   (t2.col_1 = t1.col_1 or (t2.col_1 is null and t1.col_1 is null))
  7  	      and     (dbms_lob.compare (t1.col_2, t2.col_2) = 0 or (t1.col_2 is null and t2.col_2 is null)))
  8  and     not exists
  9  	     (select  *
 10  	      from    table_test_1 t3
 11  	      where   (t3.col_1 = t1.col_1 or (t3.col_1 is null and t1.col_1 is null))
 12  	      and     t1.rowid > t3.rowid
 13  	      and     (dbms_lob.compare (t1.col_2, t3.col_2) = 0 or (t1.col_2 is null and t3.col_2 is null)))
 14  /

COL_1                          COL_2
------------------------------ ------------------------------
3                              30
4                              40
6                              null
null                           60

4 rows selected.

Report message to a moderator

Re: How to use minus, union for CLOB datatype column in Oracle? [message #646988 is a reply to message #646979]

Sat, 16 January 2016 06:56

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Yes, this will work.

SY.

Report message to a moderator

Previous Topic:	interview question
Next Topic:	ANSI SQL STATEMENTS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 28 10:15:48 CDT 2025