RE: Determining char/varchar2 column length
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Jul 2004 17:11:17 -0400
Do you know which character sets and languages you will see?
If you just use vsize on your existing data, then later you might get a
longer character. I can't remember exactly, but I think some Southeast Asian
language ended up with the longest encoding.
If your languages are pretty much set, then I'd just go with longest
character from those languages for your purpose of generating string limits
in programs as firewalls. If you're talkin' C, use a definition in a .h file
so you can easily change it globally if you add Javanese later.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Adam Donahue
Sent: Wednesday, July 14, 2004 4:01 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Determining char/varchar2 column length
It's helpful to know the length for automation purposes (building a
program that enforces an upper-limit on the size of a string variable,
and automatically updates this limit when there's a change to the
database definition, for example). We don't want to have to go in and
update a bunch of code just because we increased the length of a field
from, say, 15 to 30 characters.
Won't "avg_col_len" /be/ the average column length? I don't want the
average, I want the maximum number of characters the column would permit.
Thanks,
Adam
Mercadante, Thomas F wrote:
>Adam,
>
>Gather stats for your table and look in the USER_TAB_COLUMNS view. There
is
>a column named AVG_COL_LEN which will give you the answer.
>
>I'm just curious. Why does the length of a field matter?
>
>I see this question come up and can't for the life of me guess what you are
>trying to do.
>
>If you are trying to estimate the amount of space a certain amount of data
>would take, then gathering stats for the table and calculating using the
>average row length (that is available in the USER_TABLES view) is much more
>accurate.
>
>Lengths of individual columns reminds me of the old Cobol record length
>formatting days.
>
>Just wondering what you are trying to get at.
>
>Tom Mercadante
>Oracle Certified Professional
>
>
>-----Original Message-----
>From: Adam Donahue [mailto:adonahue@xxxxxxxxxxx]
>Sent: Wednesday, July 14, 2004 3:30 PM
>To: oracle-l@xxxxxxxxxxxxx
>Subject: Determining char/varchar2 column length
>
>
>Folks,
>
>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?
>
>For 8i, character length = data_length
>
>For 9i, character length = char_length (which /may/ equal data_length
>depending on the value of char_used)
>
>Question, again, is whether there's a single query that can be run
>against both databases to accurately determine the length of a
>character-based column.
>
>Thanks,
>
>Adam
>
>----------------------------------------------------------------
>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
>-----------------------------------------------------------------
>----------------------------------------------------------------
>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
>-----------------------------------------------------------------
>
>
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
