Oracle: How To Changes a Fields Data Type

plumbing supplies, pex tubing
copper fittings, pex fittings

Oracle: How To Changes a Fields Data Type

This article will require a little knowledge when using Oracle database. An Oracle database is is an object-relational database management system. An object-relational database, or object-relational database management system, is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, just as with proper relational systems, it supports extension of the data model with custom data-types and An object-relational database can be said to provide a middle ground between relational databases and object-oriented databases.

This article will explain the best way to change field data type. When dealing with Oracle we must recognize that due to complexity of it over smaller databases like Microsoft access or MySQL. Unfortunately, since Oracle handles things differently changing a fields data type is a little more complicated.

Here is an Oracle the command to lengthen a text field (called VARCHAR2 in Oracle) is as follows:

ALTER TABLE mytable MODIFY myfield VARCHAR2(20)

You have just made the field "myfield" a size of 20. You can change this field to any size so as long as there is no data in the table that already has a character length that is longer.

But how would you convert this into a number field? You could simply do this command:

ALTER TABLE mytable MODIFY myfield NUMBER(5)

Simply typing this will produce an error if there was already data in the database. In order to circumvent this problem, we will have to do several steps.

The first step is to create a new column and call it something similar to the column that we want to change:

ALTER TABLE mytable ADD myfield_temp NUMBER(5)

Next, you want to update this temporary field with data from the other field.

UPDATE mytable SET myfield_temp = myfield

If you receive an "Invalid Number" error then you will need to go back and check the myfield column's data for any entry that isn't a number. Sometimes text can get in there by mistake and that would have to be addressed.

Next, we need to rename the column myfield to something different myfield_old and then rename the myfield_temp column to myfield

ALTER TABLE mytable RENAME COLUMN myfield TO myfield_old
ALTER TABLE mytable RENAME COLUMN myfield_temp TO myfield

Finally, do one last query to make sure the data was moved over and if all looks good, then issue one final command.

ALTER TABLE mytable DROP COLUMN myfield_old

That should do it, now to summarize these steps:
ALTER TABLE mytable ADD myfield_temp NUMBER(5)
UPDATE mytable SET myfield_temp = myfield
ALTER TABLE mytable RENAME COLUMN myfield TO myfield_old
ALTER TABLE mytable RENAME COLUMN myfield_temp TO myfield
ALTER TABLE mytable DROP COLUMN myfield_old

This sounds a little cumbersome to do these steps but when you are dealing with millions of rows in a table this is the only sound way of accomplishing this. You could group these queries into a stored procedure and simply pass the parameters but you need to be careful that one of these queries doesn't error out before dropping columns. You could simply keep the column just to back track if something doesn't seem right.

Share:

Enter Feedback/Comment for this Article / Contact Us