Re: Determining the defined maximum length of a VARCHAR field

Rene Nyffenegger wrote:

> > Michael Young wrote:
> >
> >> Is there an easy way to determine the defined length of a
> >> VARCHAR/VARCHAR2 field in Oracle SQL? I write a lot of interfaces
> >> that import data, and many times I'd like to simply truncate the
> >> incoming text data to the length of the existing varchar column in a
> >> table. I know I can query 'user_tab_columns', but it seems that there
> >> should be a built-in function that takes the table name and the
> >> varchar column name and returns the defined maximum length for the
> >> column.
> >
> > In PL/SQL variables should almost never be defined as VARCHAR2, and never
> > as VARCHAR.
> >
> > Never VARCHAR because Oracle makes no promise to support it in the future.
> > Almost never VARCHAR2 because any VARCHAR2 that relates to a columns
> > should be defined as table_name.column_name%TYPE.
> >
> > This not only eliminates the need to know the size ... it eliminates the
> > possibility of an application breaking if the column size is altered.
> >
> > Daniel Morgan
>
> If I have understood the OP correctly, it is this very size of the varchar2 he
> wanted to know.
>
> declare
> unknown_size table_name.column_name%TYPE;
> long_str varchar2(100);
> begin
> long_str := 'akjf alkdj fkjkejflakjdfk ajkdf';
> unknown_size := long_str;
> end;
> /
>
> As he doesn't know if unknown_size is larger then long_str, he doesn't know
> if the programm bombs at unknown_size := long_str and should take some
> measures such as unknown_size:=substr(long_str,1,sizeof(unknown_size))
> where sizeof would be the requested function.
>
> Rene Nyffenegger
>
> --
> Projektleitung und Entwicklung in Oracle/C++/C# Projekten
> http://www.adp-gmbh.ch/cv.html

What the OP wrote was: " Is there an easy way to determine the defined length of a

VARCHAR/VARCHAR2 field in Oracle SQL?" The operative word in the post is "field".

Nothing was said about variables.

Daniel Morgan Received on Wed Mar 12 2003 - 19:36:30 CST