Home » SQL & PL/SQL » SQL & PL/SQL » How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL (Oracle 10g)
How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562629] Thu, 02 August 2012 23:59 Go to next message
kumar0828
Messages: 22
Registered: August 2012
Location: Bengaluru
Junior Member
I have one query regarding how to retrieve the CLOB data.

The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.
Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset.
But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.

Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error.

I know that DISTINCT keyword cannot be used for CLOB datatypes.

So can anyone help me out here with any work around/ alternate way to get the CLOB value in the SELECT QUERY.

So please help me out in this.
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562636 is a reply to message #562629] Fri, 03 August 2012 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset.


So most likely your query is wrong.

Regards
Michel
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562640 is a reply to message #562636] Fri, 03 August 2012 00:51 Go to previous messageGo to next message
kumar0828
Messages: 22
Registered: August 2012
Location: Bengaluru
Junior Member
Hi Micheal,

I actually meant that since there are lot of dependency on the tables there will be duplicate values in the resultset before using distinct keyword. So to avoid the duplicate values i need to use distinct keyword.

Since there are CLOB data columns I'm not able to use distinct keyword.

I hope now you got my question.

Thanks,
Kumar
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562643 is a reply to message #562640] Fri, 03 August 2012 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I actually meant that since there are lot of dependency on the tables there will be duplicate values in the resultset before using distinct keyword


So either your model or your query is wrong.
As you provide NO information we can't help more.

Regards
Michel
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562646 is a reply to message #562643] Fri, 03 August 2012 01:19 Go to previous messageGo to next message
kumar0828
Messages: 22
Registered: August 2012
Location: Bengaluru
Junior Member
Hi Michel,

I just want to know how to retrieve the CLOB data columns when the DISTINCT keyword has been used in the select statement.

Thanks,
Kumar
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562649 is a reply to message #562646] Fri, 03 August 2012 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can't use DISTINCT on a CLOB.

Regards
Michel
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562651 is a reply to message #562649] Fri, 03 August 2012 01:39 Go to previous messageGo to next message
kumar0828
Messages: 22
Registered: August 2012
Location: Bengaluru
Junior Member
Quote:
I know that DISTINCT keyword cannot be used for CLOB datatypes.


I have mentioned it in the initial post only Michel.

I want the work around or an alternative to work on CLOB datatypes.
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562652 is a reply to message #562651] Fri, 03 August 2012 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Something like:
select <clob column and other ones>
from <whatever is needed to get the columns>
where <other columns> in 
     (select <other columns> from <another whatever is needed to get the other columns>)

Regards
Michel
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562671 is a reply to message #562629] Fri, 03 August 2012 05:34 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
kumar0828 wrote on Fri, 03 August 2012 00:59
So can anyone help me out here with any work around/ alternate way to get the CLOB value in the SELECT QUERY.


SQL> create table tbl(c clob)
  2  /

Table created.

SQL> insert
  2    into tbl
  3    select  dname
  4      from  dept d,
  5            emp e
  6      where e.deptno = d.deptno
  7  /

14 rows created.

SQL> commit
  2  /

Commit complete.

SQL> select  distinct c
  2    from  tbl
  3  /
select  distinct c
                 *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB


SQL> select  c
  2    from  tbl t1
  3    where not exists (
  4                      select  1
  5                        from  tbl t2
  6                        where t2.rowid < t1.rowid
  7                          and dbms_lob.compare(t1.c,t2.c) = 0
  8                     )
  9  /

C
--------------------------------------------------------------------------------
ACCOUNTING
RESEARCH
SALES

SQL> 


SY.
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562673 is a reply to message #562629] Fri, 03 August 2012 05:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Another method:

SQL> CREATE OR REPLACE
  2    TYPE clob_obj IS OBJECT(
  3                            c CLOB,
  4                            ORDER MEMBER FUNCTION equals(
  5                                                         p_c clob_obj
  6                                                        )
  7                              RETURN NUMBER 
  8                           )
  9  /

Type created.

SQL> CREATE OR REPLACE
  2    TYPE BODY clob_obj
  3      IS
  4        ORDER MEMBER FUNCTION equals(
  5                                     p_c clob_obj
  6                                    )
  7          RETURN NUMBER
  8          IS
  9          BEGIN
 10              RETURN dbms_lob.compare(SELF.c,p_c.c);
 11        END;
 12  END;
 13  /

Type body created.

SQL> with t as (
  2             select  distinct(clob_obj(c)) c
  3               from  tbl
  4            )
  5  select  treat(c as clob_obj).c
  6    from  t
  7  /

TREAT(CASCLOB_OBJ).C
--------------------------------------------------------------------------------
ACCOUNTING
RESEARCH
SALES

SQL> 


SY.
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562767 is a reply to message #562673] Sat, 04 August 2012 13:38 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
maybe I am missing something but...

It seems to me the problem has a basic flaw due to bad design.

COL1  COL2  COL3  CLOB
A     A     A     1234
A     A     A     5678

If your problem is that you need to distinct based on the clob values, you cannot. You can strip the first 4000 characters out of the clob and distinct on that but that is the best you can do. But this only really works if your clobs have a max size of 4000 to begin with which makes us ask why then was a clob used to store the data?

If your problem is to distinct on (as in this example) C1,C2,C3 the the question remains, once you distinct out and get one row of AAA, which clob value do you take? 1234, or 5678?

What am I missing? Kevin
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562773 is a reply to message #562767] Sat, 04 August 2012 14:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Did you read my post? I gave two methods of selecting distinct expression list if some (or all) of them are CLOB.

SY.
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562775 is a reply to message #562773] Sat, 04 August 2012 15:30 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
No, sorry. By the time I got to your reply, I was skimming. Since I don't use objects that much, it never occurred to me to do what you did. It is very good, thanks.

Aside from an issue with null:

ORA-22951: NULL returned by ORDER method

It appears to work very well. I think I will use this if you don't mind. For those who need a more generalized example with additional attributes:

drop table tbl2;

create table tbl2 (a integer,b varchar2(30),c date,d clob)
/

declare
   v1 clob;
begin
   v1 := lpad('1',32767,'1')||lpad('2',32767,'2');
   insert into tbl2 values (1,'a',sysdate,v1);
   insert into tbl2 values (1,'a',sysdate,v1);
end;
/

commit
/

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 dbms_lob.compare(SELF.c,p_c.c);
      END;
END;
/

set long 30

with t as (
             select  distinct a,b,c,clob_obj(d)  d
             from  tbl2
          )
select  a,b,c,treat(d as clob_obj).c d
  from  t
/


Looking at just the data

16:26:49 SQL> select * from tbl2;

         A B                              C                    D
---------- ------------------------------ -------------------- ------------------------------
         1 a                              04-aug-2012 16:23:14 111111111111111111111111111111
         1 a                              04-aug-2012 16:23:14 111111111111111111111111111111

2 rows selected.

Elapsed: 00:00:00.29
16:28:11 SQL> with t as (
16:28:19   2               select  distinct a,b,c,clob_obj(d)  d
16:28:19   3               from  tbl2
16:28:19   4            )
16:28:19   5  select  a,b,c,treat(d as clob_obj).c d
16:28:19   6    from  t
16:28:19   7  /

         A B                              C                    D
---------- ------------------------------ -------------------- ------------------------------
         1 a                              04-aug-2012 16:23:14 111111111111111111111111111111

1 row selected.

Elapsed: 00:00:00.20


Thanks SY. Kevin
Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562776 is a reply to message #562775] Sat, 04 August 2012 15:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Kevin Meade wrote on Sat, 04 August 2012 16:30
Since I don't use objects that much, it never occurred to me to do what you did.


You do not have to use objects. And yes, I just gave a sample of a complete solution without dealing with NULLs. So first method ajusted to account for NULLs:

SQL> create table tbl(v varchar2(10),c clob)
  2  /

Table created.

SQL> insert
  2    into tbl
  3    select  job,
  4            dname
  5      from  dept d,
  6            emp e
  7      where e.deptno = d.deptno
  8  /

14 rows created.

SQL> commit;

Commit complete.

SQL> column c format a20
SQL> select  v,
  2          c
  3    from  tbl t1
  4    where not exists (
  5                      select  1
  6                        from  tbl t2
  7                        where t2.rowid < t1.rowid
  8                          and (dbms_lob.compare(t1.c,t2.c) = 0 or (t1.c is null and t2.c is null))
  9                          and (t2.v = t1.v or (t1.v is null and t2.v is null))
 10                     )
 11  /

V          C
---------- --------------------
MANAGER    ACCOUNTING
PRESIDENT  ACCOUNTING
CLERK      ACCOUNTING
CLERK      RESEARCH
MANAGER    RESEARCH
ANALYST    RESEARCH
SALESMAN   SALES
MANAGER    SALES
CLERK      SALES

9 rows selected.

SQL> 


Second method ajusted to account for NULLs:

SQL> CREATE OR REPLACE
  2    TYPE clob_obj IS OBJECT(
  3                            c CLOB,
  4                            ORDER MEMBER FUNCTION equals(
  5                                                         p_c clob_obj
  6                                                        )
  7                              RETURN NUMBER 
  8                          )
  9  /

Type created.

SQL> CREATE OR REPLACE
  2    TYPE BODY clob_obj
  3      IS
  4        ORDER MEMBER FUNCTION equals(
  5                                     p_c clob_obj
  6                                    )
  7          RETURN NUMBER
  8          IS
  9          BEGIN
 10              RETURN CASE
 11                       WHEN SELF.c IS NULL AND p_c.c IS NULL THEN 0
 12                       ELSE NVL(dbms_lob.compare(SELF.c,p_c.c),1)
 13                     END;
 14        END;
 15  END;
 16  /

Type body created.

SQL> column c format a15
SQL> with t as (
  2             select  distinct v,
  3                              clob_obj(c) c
  4               from  tbl
  5            )
  6  select  v,
  7          treat(c as clob_obj).c as c
  8    from  t
  9  /

V          C
---------- ---------------
ANALYST    RESEARCH
CLERK      ACCOUNTING
CLERK      RESEARCH
CLERK      SALES
MANAGER    ACCOUNTING
MANAGER    RESEARCH
MANAGER    SALES
PRESIDENT  ACCOUNTING
SALESMAN   SALES

9 rows selected.

SQL> 


SY.

[Updated on: Sat, 04 August 2012 15:56]

Report message to a moderator

Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562822 is a reply to message #562671] Mon, 06 August 2012 02:40 Go to previous messageGo to next message
kumar0828
Messages: 22
Registered: August 2012
Location: Bengaluru
Junior Member
Hi Solomon\Kevin,

Thanks for your help.
Your methods did help me out.

[Updated on: Mon, 06 August 2012 02:40]

Report message to a moderator

Re: How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL [message #562823 is a reply to message #562822] Mon, 06 August 2012 02:54 Go to previous message
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
Senior Member
Just a little extra, on the chance someone might find it useful: DBA_LOGSTDBY_NOT_UNIQUE lists all tables which do not have a primary key (or unique not null columns), flagging those which include an unbounded column. So you can determine which tables will have this problem, perhaps before it hits you:
orcl> create table withoutlob(c1 number,c2 varchar2(2));

Table created.

orcl> create table withlob(c1 number, c2 clob);

Table created.

orcl> select * from DBA_LOGSTDBY_NOT_UNIQUE where owner='JON';

OWNER                          TABLE_NAME                     B
------------------------------ ------------------------------ -
JON                            PARTS                          N
JON                            WITHOUTLOB                     N
JON                            WITHLOB                        Y

orcl>
Previous Topic: Please help me in writing a query
Next Topic: subquey execution
Goto Forum:
  


Current Time: Sun Aug 24 10:16:16 CDT 2025