Convert column from Number(9) to varchar2(3) in SQL

5 pts.
SQL tables
What is the quickest way of achieving a total column datatype change from number(9) to varchar(3)? I have a few million rows in this table and SQL will run forever ....

Answer Wiki

Thanks. We'll let you know when a new response is added.

In Oracle, I would use the bulk collect capability, as follows:

1. Create a copy table of the source (empty with no data), as in:
create table MyClone as select * from OldTable where 0=1;

2. Alter the table to change the data type of the column from Number(9) to Varchar2(3)
alter table MyClone modify (MyColumn varchar2(3));

3. Load the table using the bulk collect:

type temp_tab is table of OldTable%ROWTYPE INDEX BY PLS_INTEGER;
my_temp_table temp_tab;
select * BULK COLLECT INTO my_temp_table FROM OldTable;
FORALL temp_index IN my_temp_table.FIRST .. my_temp_table.LAST
INSERT INTO MyClone VALUES my_temp_table(temp_index);

4. Rename the old table (or drop it if you are very confident – grin) (also rename or drop the old indexes and other constraints)

5. Rename MyClone to OldTable

6. Apply the necessary indexes and primary keys

Voila – you are set.

A couple of notes…
Obviously, this will only work if all the values in the old Number(9) column are values that are less that 1000.
Apply the indexes and primary keys at the end of the process, as this will generally speed up the copy/bulk load.

I do not have an Oracle system handy to test this code, so there may be some minor “oops” in it. If FORALL doesn’t like the implicit data conversion, the process is quite a bit more complicated, as you may need to create a separate collection for each column of data.

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    I agree with Kccrosser. That could be your better option, but keep in mind that when using this approach, you are temporary storing the complete contents of the table in memory. Depending on the available resources on the server, and the amount of data in the table, you might want to perform the bulk fetch / insert in chunks, using the LIMIT clause. Let us know if you need further help.
    83,690 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: