Change field length or type in a database

If I has a table with a field
   1) length = 10
   2) type = int
the table contains some data.
Now, I decide to change the field
   1) length = 20
   2) type = varchar

Can I change the field length/type without lossing my data inside the table ?
Assume I use Oracle or MsSQL databse.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Not sure how oracle handles it, but SQL Server will automatically conver the data-types if you issue an ALTER TABLE statement or change the structure through EM manager.

You should be ok since you are increasing the size of a field and converting a number to a string.  You would probably have more problems if you were decreasing the size of a field or converting a string to a number.


From data type                    To data type    Result
int, smallint, or tinyint          char           *
                                   varchar        *
                                   nchar          E
                                   nvarchar       E
money, smallmoney, numeric,
decimal, float, or real            char           E
                                   varchar        E
                                   nchar          E
                                   nvarchar       E

* --- Result length too short to display.
E --- Error returned because result length is too short to

For Oracle
You can change any column’s datatype if all rows for the column contain nulls. However, if you change the datatype of a column in a materialized view container table, the corresponding materialized view is invalidated.
You can omit the datatype only if the statement also designates the column as part of the foreign key of a referential integrity constraint. Oracle automatically assigns the column the same datatype as the corresponding column of the referenced key of the referential integrity constraint.
You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the columns contain nulls. You can reduce the
size of a column’s datatype as long as the change does not require data to be modified. Oracle scans existing data and returns an error if data exists that exceeds the new length limit.
You can modify a DATE column to TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE. You can modify any TIMESTAMP WITH LOCAL TIME ZONE to a DATE column.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
for oracle

alter table <tablename> modify <coloumn name> varchar2(20);
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.