Re: Determining the defined maximum length of a VARCHAR field

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 12 Mar 2003 23:58:32 GMT
Message-ID: <b4ohj7$1u94ek$1@ID-82536.news.dfncis.de>

> 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

Received on Wed Mar 12 2003 - 17:58:32 CST