| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Determining char/varchar2 column length
Adam
Try select max(vsize(column_name)) from table;
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
I said it "looked" clear - Riddick
-----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 3: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
>-----------------------------------------------------------------
>
>
-- 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 - 15:10:52 CDT
 |
 |