Re: 10g field varchar size

From: Anurag Varma <avoracle_at_gmail.com>
Date: 14 Aug 2006 08:45:27 -0700
Message-ID: <1155570327.173729.277460@m73g2000cwd.googlegroups.com>

frank.van.bortel_at_gmail.com wrote:
> DA Morgan schreef:
>
> > Frank van Bortel wrote:
> > > DA Morgan schreef:
> > >
> > > But you cannot use a clob in a where clause
> >
> > CREATE TABLE demo (
> > clobcol CLOB);
> >
> > INSERT INTO demo (clobcol) VALUES ('This is a test');
> > INSERT INTO demo (clobcol) VALUES ('This is not a test');
> > COMMIT;
> >
> > desc demo
> >
> > SELECT * FROM demo;
> >
> > SELECT *
> > FROM demo
> > WHERE dbms_lob.substr(clobcol, 32767, 1) LIKE '%not%';
> >
>
> Correct - but not directly; like
> where <clob_col> like ...
> You have to use a function

I don't think so.

ORA92> select * from demo where clobcol like '%test%';

CLOBCOL

---

This is a test
This is not a test

ORA92> desc demo
 Name
Null? Type

---

• -------------------------------------------- CLOBCOL CLOB

A look in stdspec.sql reveals:

• LIKE -- function 'LIKE' (str CLOB CHARACTER SET ANY_CS, pat CLOB CHARACTER SET str%CHARSET) return BOOLEAN;

So like does allow clob and has done that since 9i.

Anurag Received on Mon Aug 14 2006 - 10:45:27 CDT