Re: Problem searching CLOB field types

"Marval Software" <support_at_marval.co.uk> wrote in message news:1026491830.5867.0.nnrp-10.c2d9c095_at_news.demon.co.uk...
> 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
>
> Does anyone have any guidance ?
>
> Thanks in advance,
>
> Greg Pritchett
>
>
>

Use dbms_lob.instr and/or the contains function available in Oracle Intermedia.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address