RATE THIS ANSWER
+2
Click to Vote:
- 2
0
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.
