Re: Determining Varchar Maximum Length

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 19 Jan 2003 12:05:40 GMT
Message-ID: <b0e4ak$nd85p$4@ID-82536.news.dfncis.de>

> Hi,
>
> I am writing a server in Java that connects to an Oracle database. I have a
> table called USERS. A column in USERS is called ID. ID has type
> VARCHAR(10). My problem is how do I write code that gets the size 10.
> Could it be done in SQL, PL/SQL, or some method from Oracle JDBC driver?
> Thanks a lot.
>
> Sam Lai

Sam,

you could try to query user_tab_columns;

set feedback off

create table l (

  a varchar2(10),
  b varchar2(20),
  c varchar2(30),

  d number
);

create or replace
  function get_length_varchar2

             (p_table_name  in Varchar2,
              p_column_name in Varchar2) return number

  is
    v_data_type user_tab_columns.data_type%type;     v_data_length user_tab_columns.data_length%type;   begin

    select

      data_type, 
      data_length
    into
      v_data_type,
      v_data_length
    from
      user_tab_columns
    where
      table_name = p_table_name and
      column_name= p_column_name;

    if v_data_type <> 'VARCHAR2' then
      return -1;

    end if;

    return v_data_length;
end;
/

select get_length_varchar2('L','A') "Length A" from dual;
select get_length_varchar2('L','B') "Length B" from dual;
select get_length_varchar2('L','C') "Length C" from dual;
select get_length_varchar2('L','D') "Length D" from dual;

drop table l;
drop function get_length_varchar2;

Rene  

-- 
  no sig today

Received on Sun Jan 19 2003 - 06:05:40 CST