Re: Problems searching CLOB fields

Marval Software wrote:
>
> We are trying to use a SQL statement to find a record containing some text.
> In ORACLE, when the text field is of type CHAR or VARCHAR2 the statement
> below functions correctly.
>
> select ID from MYTABLE where CHARFIELD like '%hello%'
> select ID from MYTABLE where VARCHARFIELD like '%hello%'
>
> but as soon as we reference a field of type CLOB which contains ASCII text,
> the SQL fails with
>
> szSqlStr = "select ID from MYTABLE where CLOBFIELD like '%hello...",
> cbSqlStr = -3
> Return: SQL_ERROR =-1
> szSqlState = "HY000", *pfNativeError = 932, *pcbErrorMsg = 53
> MessageText = "[Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes

Try using the dbms_lob package, e.g.

select ID from MYTABLE where dbms_lob.instr(CLOBFIELD, 'hello')>0;

Hth,

Knut Received on Fri Jul 12 2002 - 19:01:43 CEST