Migrate applications from SQL Server to Oracle

Database: SQL Server 2008, Oracle 9i
Tool: trial version of Sybase powerdesiner 12, PL/SQL developer 7.1.5

There are two main tasks for Migration:
1. Generate a script for creating an Oracle database based on the SQL Server database
2. ModifyCodeSQL statement in

Generate a script for creating an Oracle database based on the SQL Server database
1. Use powerdesiner to reverse engineer the SQL Server database to generate a physical data model
2. Create a logical model based on the physical data model generated by Reverse Engineering
3. Generate the physical model of the Oracle database version based on the Logical Model.
4. generate an Oracle database creation script based on the physical model of the Oracle database version.
5. Modify the Oracle database creation script according to the Oracle naming rules and Data Type Definitions.
6. Run the command in PL/SQL developer to verify its correctness.

Modify the SQL statement in the code
1. Commonly Used square brackets in SQL Server include the table name and field name. to Oracle, remove these square brackets.
2. Change the "@" symbol before the parameter change to ":" symbol.
3. Replace the proprietary SQL Server methods and functions with the corresponding Oracle methods and functions
4. Run the SQL statement in PL/SQL developer to verify its correctness.

FAQs during conversion
1. The table name and field name use the keywords in Oracle.
2. The nvarchar data type in SQL Server can store 4000 Chinese characters, and the nvarchar/nvarchar2 Data Types in Oracle can only store 2000 Chinese Characters

Word
3. The index name and foreign key name exceed 30 characters
4. For the script generated by powerdesigner, the table name and field name are enclosed in quotation marks and must be removed.
5. SQL Server Query statements support returning multiple result sets. Oracle does not support returning multiple result sets in. The cursor is used to return multiple result sets in Oracle.
6. The insert Statement of SQL server does not include the into keyword. It must be added to Oracle. Otherwise, an error is reported.
7. Add square brackets for SQL statements in SQL Server. Remove the square brackets in Oracle.
8. ASP. NET reads an empty string from Oracle and then becomes null.
9. If the XML type in SQL Server is converted to Oracle, you must use the nclob type to avoid errors.

Reference:
Oracle Data Type
Http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/ SQL _elements2a.htm#45443

Oracle keywords
Http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_keywd.htm#617080

Oracle naming rules
Http://q.yesky.com/group/review-17556825.html

Brief summary of migrating the company system from sqlserver 2 K to Oracle 10g
Http://www.cnblogs.com/yiping06993010/archive/2007/08/02/840220.html