Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    13

    Unanswered: column length consistency in tables

    I am having schema where I want to alter some of the table structure as some of the fields in my tables like
    table1

    ADDRESSNO VARCHAR2(5)
    STREET1 VARCHAR2(50)
    STREET2 VARCHAR2(50)

    Table2
    ADDRESSNO VARCHAR2(10)
    STREET1 VARCHAR2(30)
    STREET4 VARCHAR2(30)


    I want to make all ADDRESSNO to VARCHAR2(5) in table2 and all street in table1 to VARCHAR2(30).

    select * from all_tab_columns where COLUMN_NAME='STREET1' AND OWNER='schema_name';
    output of this query shows different length for same column name ,
    So the different tables meant for storing same information but there lengths is varying .
    I want to maintain structural consistency in my column lengths.For doing these kind of changes I have to first make a copy of table in some temp table . then truncate the table alter the length
    alter table table2 modify street2 varchar2(30);
    and then again insert the data .

    I have to do many of these kind of structure changes .
    any suggestions. Any tools for seeing this kind of descripencies in my database and to do correction.
    thanks in advance

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any tools for seeing this kind of descripencies in my database
    Yes, it is called SQL
    >and to do correction.
    Yes, it is called SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Instead of decreasing the length of the columns, which could run into length problems, why not increase them to the largest size. While the application that uses these tables have to support what ever length you are using, you will get into problems with the application no matter if you increase or decrease the existing columns and increasing them is the easiest and doesn't cause any problems with existing data. And speaking as a programmer of 30 years, bigger is almost always better when you are setting up columns to store data. never plan the the smallest size, plan for the biggest and then add some growth space.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •