Re: Determining char/varchar2 column length

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Wed, 14 Jul 2004 23:19:16 +0300
Message-ID: <009301c469df$d65dee00$63a423d5@porgand>

> Is there a single query or builtin PL/SQL routine that allows one to
> calculate the (character) length of a column that works for both 8i and
> 9i?

SQL> select a, vsize(a) from t;

A VSIZE(A)
---------- ----------

a                   1
aa                  2
aaa                 3
äää                 9

Note that this only accounts for the actual data size inside the column of a row, not the column overhead, which may be 1 or 3 for an unchained varchar2 field, depending on its size (1 byte if column data length is less or equal to 250 bytes, 3 if more)

If the column happens to be split into multiple parts due row chaining, then additional 1 or 3 overhead bytes per chained piece will be used. Also the row itselt has 4 additional bytes per chained piece (excluding the last piece) in case of chained or migrated row.

>
> For 8i, character length = data_length
>
> For 9i, character length = char_length (which /may/ equal data_length
> depending on the value of char_used)

No, the character/data length ratio depends on whether you're using a single or multibyte character set, not version.

Tanel.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jul 14 2004 - 15:17:11 CDT