Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Determining char/varchar2 column length

RE: Determining char/varchar2 column length

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 14 Jul 2004 17:11:17 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKEEKHFAAA.mwf@rsiz.com>


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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Adam Donahue Sent: Wednesday, July 14, 2004 4:01 PM
To: oracle-l_at_freelists.org
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_at_opsware.com]
>Sent: Wednesday, July 14, 2004 3:30 PM
>To: oracle-l_at_freelists.org
>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_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
>-----------------------------------------------------------------
>----------------------------------------------------------------
>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
>-----------------------------------------------------------------
>
>



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
-----------------------------------------------------------------


----------------------------------------------------------------
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 - 16:07:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US