Re: Clarification needed about CLOB

On Thu, 17 Feb 2005 03:22:10 -0800, luttappi wrote:

> Hi All,
> I need to know the maximum size of CLOBs in a multibyte
> characterd enviornment. I know the maxium size of CLOB is 4 gb. So in
> a single byte charaters set it can store 4 GB of characters. So in a
> multibyte character set database it will be storing less number of
> characters..for eg. In a fixed 2 byte character set it could only
> store 2 GB of characters. So the maximum string length is 2 GB of
> characters. What will be the maximum string length in a variable
> length character set? If we are storing japanese character set which
> uses 2 bytes to 4 bytes i hope the maximum string length of CLOB vary
> between 1 GB to 2 GB of characters. Pls correct me if i am wrong.
> Thanks in advace
>
> Rgds
> Muneer

Database limits are normally published in the Oracle Database 'Reference' manual (not 'SQL Reference' or 'Intermedia Reference', just 'Reference' in the online docco index at http://docs.oracle.com for Oracle9iR2)

However for the individual data types you would go to the 'SQL Reference' where, in Chapter 2 under the 'Datatypes' section you find a table giving the exact sizing info.

For multibyte character, you are limited to the same storage size as the single byte charater set. In 9iR2 CLOB is 4GB and therefore 4G char, whereas NCLOB is 4GB and appropriately reduced characters if you start spilling into multi-byte chars as will happen in japanese.

Similarily, VARCHAR2 & NVARCHAR2 are 4,000 Bytes, which equates to 4,000 single byte char or 1,000 4-byte char or just about anything in between. 2,000 Bytes for the CHAR/NCHAR fixed-length definition. Amusingly CHAR and VARCHAR2 allow you to specify size in 'CHAR' or 'BYTE' semantics and 'CHAR' semantics seems to maake it identical to the N-variety.

Specific reference is
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45504

hth/FGB
(was there anything else we could read on your behalf?) Received on Thu Feb 17 2005 - 05:54:31 CST