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: Adam Donahue <adonahue_at_opsware.com>
Date: Wed, 14 Jul 2004 13:01:02 -0700
Message-ID: <40F590FE.9070504@opsware.com>


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
-----------------------------------------------------------------
Received on Wed Jul 14 2004 - 14:59:24 CDT

Original text of this message

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