Re: CLOB and NVL

Ron Reidy <rereidy_at_indra.com> wrote in message news:<3BE71323.C1F43B34_at_indra.com>...
> Lisa T wrote:
> >
> > Is it possible to use NVL or DECODE with a CLOB in Oracle 8i?
> >
> > For example,
> > select nvl(clob_column,'None') from table;
> >
> > currently returns
> >
> > ERROR at line 1:
> > ORA-00932: inconsistent datatypes
> >
> > Thanks. Lisa
> This error is because the 2 arguments to the NVL() must be the same (a
> clob is not a varchar2). How about NVL2() instead of NVL()? My guess
> is this also holds for DECODE() as well.

NVL2 returns the same error, and there appears no way to convert varchar2 into CLOB, based on the following TO_LOB documentation from Oracle: TO_LOB converts LONG or LONG RAW values in the column long_column to LOB values. You can apply this function only to a LONG or LONG RAW column, and only in the SELECT list of a subquery in an INSERT statement (see "INSERT").

Pursuing the NVL(CLOB,'None') convention to get around a ColdFusion problem that will retrieve values from another row when the CLOB value of the current row is null.

Thanks for any additional insight you can provide. Lisa Received on Mon Nov 12 2001 - 13:37:59 CST