By Bill Ramos and Vishal Soni, Advaiya Inc.
This blog post covers the reason why SQL Server Migration Assistant (SSMA) for Oracle cannot convert EXCEPTION_INIT.
Exception handling is a programming language construct or mechanism designed to handle the occurrence of exceptions, special conditions that change the normal flow of program execution. One can use the pragma EXCEPTION_INIT to associate exception names with other Oracle error codes that one can anticipate.
Error O2SS0054 Unable to convert EXCEPTION_INIT
Whenever you try to convert PL/SQL code having an “EXCEPTION_INIT” exception with a user defined exception name in it, SSMA is unable to convert EXCEPTION_INIT because it doesn’t find an exception with the same name in the parameter section. Hence SSMA generates “Error O2SS0054 Unable to convert EXCEPTION_INIT”.
Consider the following example:
PRAGMA EXCEPTION_INIT(MYEXCEPTION_1, -20000);
When you execute this code in SSMA, it does not identify the user defined exception “MYEXCEPTION_1” and hence generates the error “Error O2SS0054 Unable to convert EXCEPTION_INIT”.
Note: - This is a bad code example of Oracle, as Oracle also generates an error message when you try to execute this code.
Remediation of this error is to use the same name while declaring and defining an exception. For this, update the code as follows:
PRAGMA EXCEPTION_INIT(MYEXCEPTION, -20000);
SSMA will convert the updated code with no errors.
There are several other errors related to Exceptions that you may encounter. These include the following:
For more information, check out the following references: