Re: 10g field varchar size

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 14 Aug 2006 08:39:36 -0700
Message-ID: <1155569977.702932@bubbleator.drizzle.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

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.

-- 
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 - 10:39:36 CDT