Board index » delphi » Select LIKE in Oracle CLOB field

Select LIKE in Oracle CLOB field

Hello,

I'm having problems using LIKE statement to select an information from a
CLOB field (and any other memo field for that matter) in an Oracle DataBase.
I get the following error message:
"Gerenal SQL Error. ORA-00932: Inconsistent DataTypes" (not quite sure that
this is the exact message in English because I'm using Oracle in
Portuguese).

I use, Delphi 5, BDE 5.01, Oracle 8.1.5.

I was told it is not possible to make a select using LIKE in an Oracle CLOB
field, can anyone give me a hand? Anybody knows of a way around?

Thanks

beatriz Cavalieri
Modo Novo

 

Re:Select LIKE in Oracle CLOB field


Quote
Modo Novo wrote:

> I was told it is not possible to make a select using LIKE in an Oracle CLOB
> field, can anyone give me a hand? Anybody knows of a way around?

What you were told is correct.  You can use Varchar2 for memos up to 4000
characters.  I think there's some kind of Oracle add-on that can search CLOBS,
but I don't think it works with SQL.

John Pierce

Re:Select LIKE in Oracle CLOB field


John,

As a matter of fact it is possible to make a select using LIKE in an Oracle
CLOB field.

You'd have to use the DBMS_LOB.SUBSTR function.

Modo Novo

Quote
John Pierce <[email protected]> wrote in message

news:[email protected]
Quote

> Modo Novo wrote:

> > I was told it is not possible to make a select using LIKE in an Oracle
CLOB
> > field, can anyone give me a hand? Anybody knows of a way around?

> What you were told is correct.  You can use Varchar2 for memos up to 4000
> characters.  I think there's some kind of Oracle add-on that can search
CLOBS,
> but I don't think it works with SQL.

> John Pierce

Other Threads