Re: SELECT A CLOB - GROUP BY

On Apr 7, 5:06 pm, trp..._at_gmail.com wrote:
> Is there anything that can be done with a PLSQL function to convert a
> CLOB to a VARCHAR2 to a size larger than 4000 characters? I read that
> PLSQL can handle a VARCHAR2 with 32767 charcaters, so is it possible
> to write a function to return this conversion?
>
> I found the following, but couldn't get it to compile:
>
> CREATE OR REPLACE FUNCTION GetVarchar2 (iclCLOB IN OUT CLOB)
> return VARCHAR2
> IS
>
> cnuMAX_LENGTH Constant number := 32767 ;
> nuLength Number := DBMS_LOB.getlength(iclCLOB);
> sbBuffer varchar2(32767);
>
> DBMS_LOB.read(iclCLOB,nuLength,1,bBuffer);
> return sbBuffer;
> END
>
> Thanks

If you are returning it to SQL, then no this doesn't work.

You never really answered the question: why would you want to group by the CLOB column?
What makes you think the value of one CLOB will match another? You know your data. I'm just asking because often CLOBs are used for notes, comments and other text that is unlikely to match another entry exactly.

I suspect what you will have to do is redesign your tables. You CLOB column seems to be a list of some sort. Perhaps it could be normalized to its own table, splitting the data in the CLOB to different rows. Getting the grouping you want may still be troublesome but more likely to be doable. But it depends on the nature of the data in the CLOB column.

  HTH,
Ed

--
Magic Interface, Ltd.
www.magicinterface.com
440-498-3700
Hardware/Software Alchemy