On Apr 7, 5:06 pm, trp...@[EMAIL PROTECTED]
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
|
