Re: Converting a CLOB to a String

I don't see any problem with your syntax. Are you sure there is something in there? Is it a string of spaces? What is the length?  SELECT DBMS_LOB.GETLENGTH(CLOB_FIELD)
FROM TABLE WHERE SOME_FIELD = 'some value';

If this is non-zero and it still looks like nothing is coming back, then it looks like a string of blanks. Trim the returned string and get the length. Do you get non-zero?
Dave

jpalloz_at_my-deja.com wrote:

> Dave,
>
> I got the syntax to work with no errors, but the select statement
> did not return anything. I tried both select statements below, with the
> same result, (field returned nothing, and I checked that there was
> a value in the CLOB_FIELD).
>
> Do you know what I'm doing wrong?
>
> SELECT DBMS_LOB.SUBSTR(CLOB_FIELD,1, DBMS_LOB.GETLENGTH(CLOB_FIELD))
> FROM TABLE WHERE SOME_FIELD = 'some value';
>
> SELECT DBMS_LOB.SUBSTR(CLOB_FIELD,1, 50) FROM TABLE WHERE SOME_FIELD =
> 'some value';
>
> Thanks,
>
> John P.
>
> In article <39FE3031.BEEE99D3_at_erols.com>,
> luhbey_at_erols.com wrote:
> > Check out the DBMS_LOB package. eg.
> > select DBMS_LOB.SUBSTR(clob_field,1,DBMS_LOB.GETLENGTH(clob_field))
 from
> > some_table ...
> >
> > Be careful about the length of the substring you return. 4000
 characters
> > is the limit from SQL*PLUS, or 32,000 (or is it 64,000) in PL/SQL.
> > Dave
> >
> > jpalloz_at_my-deja.com wrote:
> >
> > > Inside of a select statement does anyone know how to convert a CLOB
> > > field to a string E.G:
> > >
> > > select some_conversion_function(clob_field) from some_table where
> > > some_field = 'some_value';
> > >
> > > Thanks,
> > >
> > > John P.
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Oct 31 2000 - 19:25:21 CST