OraFAQ Forum: SQL & PL/SQL » How to use DISTINCT for CLOB data type?

Home » SQL & PL/SQL » SQL & PL/SQL » How to use DISTINCT for CLOB data type? (Oracle 11g)

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

How to use DISTINCT for CLOB data type? [message #647466]

Thu, 28 January 2016 22:52

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

Member

Hi,

I have select query runs DISTINCT on all the columns involved. A column's data type is CLOB and it is converted to CHAR using TO_CHAR function to be used in DISTINCT. I encountered issue where the data for the CLOB column is exceeding 4000 BYTES hence i cannot use TO_CHAR anymore, how can i easily convert TO_CHAR function for the CLOB column.

Basically i have to perform DISTINCT on a CLOB column. Please help.

Report message to a moderator

Re: How to use DISTINCT for CLOB data type? [message #647467 is a reply to message #647466]

Thu, 28 January 2016 23:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Consider computing a CHECKSUM on the CLOB & doing DISTINCT on the CHECKSUM.
BTW - performance could be sub-optimal [SLOW!]

Report message to a moderator

Re: How to use DISTINCT for CLOB data type? [message #647471 is a reply to message #647466]

Fri, 29 January 2016 00:40

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

Senior Member

You can't "convert to_char function for the clob column". You can use not exists and dbms_lob.compare to achieve the same results as distinct, as mentioned in one of your previous threads.

Report message to a moderator

Re: How to use DISTINCT for CLOB data type? [message #647477 is a reply to message #647471]

Fri, 29 January 2016 02:52

JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar

Member

You need to ask yourself why you need a distinct on all columns? Probably a better option is to use RANK, DENSE_RANK or ROWNUM analytic functions on the keys on your tables

Report message to a moderator

Re: How to use DISTINCT for CLOB data type? [message #647651 is a reply to message #647466]

Thu, 04 February 2016 10:34

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

Member

All,

I fixed this issue by splitting the original select query into two select queries. First query with distinct keyword excluding CLOB column, and the second query with dbms_lob.compare for CLOB column.

Thanks for all your inputs.

Report message to a moderator

Previous Topic:	Updating duplicate records with different data
Next Topic:	Comma separated column values matching

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 12 13:07:49 CDT 2025