Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Change field length or type in a database

Posted on 2003-03-18
Medium Priority
Last Modified: 2013-12-03
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.
Question by:private99
LVL 34

Expert Comment

ID: 8163944
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.


Accepted Solution

Danielzt earned 400 total points
ID: 8164060

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.


Expert Comment

ID: 8165102
for oracle

alter table <tablename> modify <coloumn name> varchar2(20);

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question