Change all column units from byte to char

Question

I've been given a task to convert a huge database in MS SQL Server to Oracle. The database has more than 150 tables with lots of constraints,defaults and relationships, that many stored procedures and triggers. Now I wonder 2 things:

1. How is this generally done? Do we have to do it manually for every object one by one or there's some better way?
2. String columns in Oracle are by default created in byte units. How can I update all the columns in all the tables from byte to char?

Thanks in advance.

Answer 1

For 2: This will produce code to update all BYTE columns to CHAR

BEGIN
    FOR x in (SELECT * FROM user_tab_columns WHERE data_type LIKE 'VARCHAR%' and CHAR_USED = 'B')
    LOOP
        DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||x.table_name||' MODIFY '||x.column_name||' '||x.data_type||'('||x.data_length||' CHAR)');
    END LOOP;
END;
/

Answer 2

I migrated MS SQL Server database to Informix with self made batch that used ADO.NET metadata to retrieve db structure. I did that because I couldn't find appropriate tool for this conversion. I wouldn't advise this approach with databases that can be migrated using exisitng tool.
So, in case of migrating to Oracle it is better to use some of existing tools like this:

http://www.oracle.com/technetwork/database/migration/sqlserver-095136.html