Re: 10g field varchar size

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Mon, 14 Aug 2006 21:40:42 +0200
Message-ID: <ebqjba$2vu$1@news3.zwoll1.ov.home.nl>

DA Morgan schreef:
> 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

>
> You mean like this?
>
> 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 clobcol LIKE '%not%';
>
> Works perfectly in 10.2.0.2.0.

Hmmm - I was in pre-8i land
with my mind, triggered by the "2000 max length for varchar2" Had some replication issues with clobs (updates over db links) as well as reporting issues (the where clause bit) on 8i.

Sorry for the confusion - must be kept off Chimay :D

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Mon Aug 14 2006 - 14:40:42 CDT