Background: One of my development project initially was on SQL Server as backend database. But consistently facing problem of ‘ODBC failed to connect’ throughout a year. The database was the SQL Server XE. Convinced to transfer my database to Oracle as it is very reliable on ODBC connection. So intended to initiate a database design migration from SQL Server to Oracle to solve the issue permanently.
Workaround: The schema design were imported into the “Enterprise Architect” by using reverse engineering feature to implement the UML stuffs. Its very easy generating code or build scripts of objects of one type of database to one another by Enterprise Architect. Workaround is below:
- Choose the Project module and the Package under which the tables are residing.
- Click the right mouse and get to the option ‘Code Engineering’ > ‘Reset DBMS Options…’
- ‘Manage DBMS Options’ window will appear.
- Select the ‘Convert DBMS Type’ option.
- Choose the ‘Current DBMS’ form drop-down as SQL Server 2008.
- Choose ‘New DBMS’ as Oracle.
- Click ‘OK’ and a warning will be shown by Enterprise Architect; choose ‘OK’ to proceed with the operation.
- Now from the Table Class properties you will see the Database as: Oracle.
- Now to start the generation of DDL for new database go to ‘Code Engineering’ > ‘Generate DDL..’ option.
- Choose a file name and location at the ‘File Generation’ option if you choose a Single File option.
- For the option ‘Individual file for each table’ there will be a script for each tables.
- If you like to include comments of columns of your table then from the ‘Options’ choose the ‘Comment Level’ as you desire.
- Choose other from ‘Options‘ as appropriate to your needs or environment.
- Finally Click the ‘Generate‘.
- Enterprise Architect will generate the table or objects DDL scripts 🙂
- Finally run the scripts in Oracle by Toad or Sql Developer client.
Note to memorize:
- As the characteristics or range of data types are different between databases you need to tune your new database tables column types and sizing according to your application need.
- If you have a bit column in SQL Server it will be transferred as Number(3) in Oracle.
- The identity column in SQL Server will be transferred into the Sequences and Triggers in Oracle to insert the Primary Key column value automatically.
- Integer in SQL Server will be transferred as Number(38) in Oracle.