When migrating Oracle databases to Microsoft SQL Server, you need to define a naming convention for the conversion of packaged procedures and functions. This step is as important as setting the schema mapping and data type mapping. This is an architecture-level decision that you should make at the very beginning of your migration project. Now, we will talk about the importance of this decision and the possible issues it may cause.
The problem
By default, SQL Server Migrating Assistant (SSMA) uses the following rule to form the names of converted procedures and functions. Basically, SSMA combines the name of the Oracle package and the procedure name and splits them with a dollar sign ($).
For example, for a TEST_PACKAGE package in Oracle with a TEST_PROCEDURE procedure, SSMA will create TEST_PACKAGE$test_procedure in SQL Server.
Possible risks
Application conversion is one of the most difficult steps in our proven 12-step database migration process. You will need to use new procedure names in your application. Basically, when you convert packaged procedures from Oracle to SQL Server, you will need to identify all the places where your application calls them. Then, you need to rewrite these calls to make the application compatible with the SQL Server database. This means that you need to replace TEST_PACKAGE.test_procedure with TEST_PACKAGE$test_procedure in the code of your application. The same procedure should be followed with third-party applications, such as reporting systems.
If you don’t define a naming convention at the very beginning of your migration project, you will have to make a lot of updates. This means you will have to rename all objects but you will also rewrite all calls of those objects in your application.
The solution
In some programming languages, you cannot use “$” in the procedure names. For example, in PowerShell, a dollar sign in front of a word denotes that the word is a variable. In this case, you need to choose another separator instead of the dollar sign ($). I would suggest using the underscore (_) as the separator for packaged procedures in the naming convention.
