Link to home
Start Free TrialLog in
Avatar of Shaunsmith
ShaunsmithFlag for United States of America

asked on

Convert an NClob to Character in Oracle

My requirement is to be able to do a distinct on a Table that has a Nclob

select distinct comment from finance_comments ;

Now since comment is a Nclob the DB does not allow me to use a distinct .

I was hoping to use "DBMS_LOB.SUBSTR" to convert it to chars .

SELECT distinct DBMS_LOB.SUBSTR(comments,4000,1)
FROM finance_comments

when I try to do this I get a 16:01:02  [SELECT - 0 row(s), 0.000 secs]  [Error Code: 6502, SQL State: 65000]  ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

Is this the right approch to this or is there a better way to be able to get distinct rows from
a table that has NClobs .
Avatar of nito8300
Flag of United States of America image

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial