Re: Determining char/varchar2 column length
- From: Adam Donahue <adonahue@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 14 Jul 2004 13:01:02 -0700
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
-----------------------------------------------------------------
Other related posts:
