Re: Memo Fields

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Thu, 15 Jan 2004 23:03:48 +0100
Message-ID: <bu725m$24s$1@news1.tilbu1.nb.home.nl>

Hans Forbrich wrote:
>
> Daniel Morgan wrote:
>

>>Hans Forbrich wrote:
>>
>>
>>>Daniel Morgan wrote:
>>>
>>>
>>>>Frank van Bortel wrote:
>>>>
>>>>
>>>>
>>>>>Daniel Morgan wrote:
>>>>>
>>>>>
>>>>>
>>>>>>Michael Hill wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>I had a guy ask me about Memo Fields in Oracle.
>>>>>>>
>>>>>>>Remember Memo fields are the text files stored outside a Microsoft
>>>>>>>Access DB when the user creates a memo field in their DB.
>>>>>>>
>>>>>>>The question was: "Does Oracle have Memo Field".
>>>>>>>The answer I gave was that Oracle had a character field that goes up to
>>>>>>>2000 charcters nad then everything else could be stored in a blob.
>>>>>>>
>>>>>>>Did I tell him correctly?
>>>>>>>
>>>>>>>Mike
>>>>>>>
>>>>>>
>>>>>>Strictly speakig no because Oracle would never limit itself to a
>>>>>>brain-dead architecture like MS Access.
>>>>>>
>>>>>>You don't mention version but for years now VARCHAR2 fields have held
>>>>>>4000 bytes (not characters Mike) and CLOB fields will hold up to 4 GB.
>>>>>>
>>>>>>Either of these can be used to hold text information and either will
>>>>>>greatly outperform MS Access capabilities.
>>>>>>
>>>>>
>>>>>And asof 9iR2 4000 Characters. UTF and all.
>>>>
>>>>4000 characters even if they characters are 3 bytes each? Are you sure?
>>>
>>>
>>>Oracle has been very careful to discriminate betwwen 'character' and
>>>'byte' in their docco.  From the SQL Reference manual for 9iR2, datatype
>>>'VARCHAR2' is 4000 bytes, datatype 'NVARCHAR2' is 4000 [unicode]
>>>characters and that means 'up to 16,000 bytes'.
>>
>>That's what I thought. I didn't think you could get more than 4K bytes
>>into a VARCHAR2.
>>

>
>
> CORRECTION: NVARCHAR2 is limited to 4000 BYTES. (Sorry)
>
> A few followup points, from
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45504
> and other areas around the SQL Reference manual:
>
> NCHAR, NVARCHAR2 use 'character' sizing and are apparently designed to
> go with unicode.
>
> CHAR, VARCHAR2 can have their size qualified "(VARCHAR2(20) CHAR" or
> "(VARCHAR2(20) BYTE" but both are limited to 4000 bytes.
>
> From what I can see, most of the 'character' functions (LTRIM, etc.) use
> 'character' rather than 'byte' semantics. Some have byte and char
> versions (SUBSTR vs SUBSTRC vs SUBSTRB, LENGTH vs LENGTHB ...).
Your are right.

What set me on the wrong track is the fact that you will not get an error an all cases, consider OID920 with AL16UTF16 national character set: scott_at_OID920.CS.NL> create table blah1 (col2 nvarchar2(4000)); create table blah1 (col2 nvarchar2(4000))

ERROR at line 1:
ORA-00910: specified length too long for its datatype

According to docco: NVARCHAR2(size)

Variable-length character string having maximum length size characters. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.

As national character set is AL16UTF16, makes sense; 2 byte characters: scott_at_OID920.CS.NL> create table blah1 (col2 nvarchar2(2000)); Table created.
QED. So here I have a table definition that FORCES me to limit the definition of the columns AT CREATION time; here's an example with an 8 bit nvarchar character set (MSWIN1252, actually): scott_at_RCV.CS.NL> create table blah(col1 nvarchar2(4000)); Table created.

QED. Makes sense, is logical.

Now, the fact that I can do this:
scott_at_OID920.CS.NL> create table blah (col1 varchar2(4000 CHAR)); Table created.

in a AL32UTF8 character set database makes me believe I can have 4000 CHARACTERS in UTF8. However:
scott_at_OID920.CS.NL> insert into blah select rpad('좿?',3001,'좿?') from dual;
1 row created.

Looks OK, doesn't it?
scott_at_OID920.CS.NL> select length(col1), lengthb(col1) from blah;

LENGTH(COL1) LENGTHB(COL1)
------------ -------------

1778 3999

Obviously... no error, nothing. rpad will silently cut at 4000 byte. But returns the correct character values. Now - the definition of varchar2: Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.

Hummm, makes you read twice to get the impact - while it could have been just as clear as the nvarchar definition, when there would just be a reference to the character set used.

This also compiles:

   1 declare
   2 l_str varchar2(32767 CHAR);
   3 begin
   4 select to_char(sysdate) into l_str from dual;    5* end;

Wonder what the restrictions of that will be - this is OT for now; created enough instances for one day. Glad I used [B|C]LOBs where ever. Except for one case; however, that table never holds more than 1 character UTF. No UTF definition will ever use 4000 bytes for that.

Again sorry for confusion I might have created

-- 

Regards,
Frank van Bortel

Received on Thu Jan 15 2004 - 16:03:48 CST