Defining a naming convention for the conversion of packaged procedures and functions is a very important step during migration of Oracle database to Microsoft SQL Server. Experienced architects usually point out that this step is as significant as setting schema and data type mapping. Naming convention should be defined at the beginning of the migration project. Let’s discuss in detail why this step is so important and what type of issues you may experience if not defining naming convention properly.
The problem
By default, SQL Server Migrating Assistant (SSMA) applies defined rules for naming converted procedures and functions. SSMA introduces the combined names of the Oracle package and the procedure that are split with a dollar sign ($).
For instance, for a TEST_PACKAGE package in Oracle with a TEST_PROCEDURE procedure, Microsoft SQL Server Migration Assistant will create TEST_PACKAGE$test_procedure in SQL Server.
Possible issues
Failure to define naming convention at the beginning of the migration project will force you to perform a lot of updates during subsequent phases of the project. You will need to rename all objects as well as rewrite all calls of these objects in your application. Apart from spending a lot of time on this task you would also deal with the increased risk of errors while performing all these changes.
Please bear in mind that application conversion requires a great level of responsibility and attention to detail. After all, it is one of the most challenging stages in the 12-step database migration process outlined by ABCloudz.
The Solution
To avoid issues mentioned above, you should first identify all the places where your application calls the convert packaged procedures. Please note that missing such calls will lead to conversion inconsistencies. To ensure smooth conversion from Oracle to SQL Server database, replace TEST_PACKAGE.test_procedure with TEST_PACKAGE$test_procedure in your applicaiton code. Note that you should apply the same naming principle to all third-party applications pertaining to your migration project.
For instance, in PowerShell, a dollar sign in front of a word makes this word a variable. In such cases we recommend you to use the underscore (_) as the separator for various packaged procedures in the naming convention.
Surely, defining a naming convention while migrating a database from Oracle to SQL Server may cause various challenges. We strongly advise to ensure correct mapping of Oracle data types to SQL Server. Also, we recommend employing our best practices for converting Oracle sequences to SQL Server. Please do not hesitate to contact us if you have any other questions pertaining to Oracle to SQL Server database migration.
