5 pts.
0
Q:
Convert column from Number(9) to varchar2(3) in SQL
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 ....
ASKED: Jun 30 2009  2:26 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0
1820 pts.
0
A:
 RATE THIS ANSWER
+2
Click to Vote:
  •   2
  •  0
  • AddThis Social Bookmark Button
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:

declare
type temp_tab is table of OldTable%ROWTYPE INDEX BY PLS_INTEGER;
my_temp_table temp_tab;
begin
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);
commit;
end;

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.
Last Answered: Jul 1 2009  3:50 PM GMT by Kccrosser   1820 pts.
0
0
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29340 pts.  |   Jul 1 2009  5:56PM GMT

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.

 
0