6

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.

Mikayil Abdullayev
  • 12,117
  • 26
  • 122
  • 206

3 Answers3

9

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;
/
grokster
  • 5,919
  • 1
  • 36
  • 22
  • 2
    In my case, I had to use `EXECUTE IMMEDIATE` instead of `DBMS_OUTPUT.PUT_LINE` – Ellone Mar 16 '18 at 11:53
  • Yes, that will run the code. The script as is will produce the code that can then be run – grokster Mar 17 '18 at 14:38
  • 1
    @Ellone the problem is probably that you didn't activate the DBMS_OUTPUT, check this [answer](https://stackoverflow.com/a/10434589/1218618) to activate it :). grokster +1 for the answer thanks! – albciff May 13 '19 at 12:06
  • Just you have to put `set serveroutput on format wrapped;` before script to be able to get it printed to your console. – Mohammed Salah Sep 10 '20 at 14:58
1

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

Filip
  • 3,257
  • 2
  • 22
  • 38
  • Thank you body. The link you provided is really awesome. I am using SQL Devloper for Oracle. But I was not aware of its migration capability. Now, I realise that I've wasted lots of time transporting the tables. – Mikayil Abdullayev Nov 17 '11 at 08:42
0

And to avoid the errors that might occur if you have views:

    BEGIN
    FOR X IN (SELECT C.TABLE_NAME, 
                     C.COLUMN_NAME,
                     C.DATA_TYPE,
                     C.DATA_LENGTH
                FROM USER_TAB_COLUMNS C
               WHERE DATA_TYPE LIKE 'VARCHAR%' 
                AND CHAR_USED = 'B'
                AND NOT EXISTS (SELECT 1 FROM USER_VIEWS V WHERE V.VIEW_NAME = C.TABLE_NAME))
    LOOP
        EXECUTE IMMEDIATE('ALTER TABLE '||X.TABLE_NAME||' MODIFY '||X.COLUMN_NAME||' '||X.DATA_TYPE||'('||X.DATA_LENGTH||' CHAR)');
    END LOOP;
    END;
Mulga
  • 11
  • 1