Re: 10g field varchar size

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 13 Aug 2006 11:01:31 -0700
Message-ID: <1155492092.187807@bubbleator.drizzle.com>

Frank van Bortel wrote:

> DA Morgan schreef:

>> timasmith_at_hotmail.com wrote:
>>> Hi,
>>>
>>> I apologize I dont know this but when I last designed a schema in
>>> Oracle the max varchar2 size was 2000 characters otherwise you had to
>>> use Long to get something larger.
>>>
>>> I believe there are several choices for huge character or binary
>>> fields, which require special utilities to qualify in a where clause.
>>>
>>> My question in 10g+ is
>>>
>>> a) What is the largest varchar field I can have without losing the
>>> ability to qualify it in a where clause
>>>
>>> b) Is there any restriction on using say clob for many fields on a
>>> single table?
>>>
>>> thanks
>>>
>>> Tim
>>
>> The docs are at http://tahiti.oracle.com
>>
>> VARCHAR2 in a table is 4K
>> VARCHAR2 in a variable is 32K
>> CLOB will likely do what you want and can be many GB. No restriction.
>
> 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%';

set serveroutput on

DECLARE
  clobvar CLOB := '%not%';
  clobres CLOB;
BEGIN
   SELECT clobcol
   INTO clobres
   FROM demo
   WHERE clobcol LIKE clobvar;

   dbms_output.put_line(clobres);
END;
/

Am I misunderstanding the question?

-- 
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 Sun Aug 13 2006 - 13:01:31 CDT