Re: Determining char/varchar2 column length
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Jul 2004 23:19:16 +0300
> 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
