Frank van Bortel wrote:
> 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
I'll help you. Just ship the Chimay to: .... ;-)
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Aug 14 2006 - 15:23:43 CDT
