Migrating Oracle to SQL Server using SSMA - Error O2SS0054 Unable to convert EXCEPTION_INIT
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
Background
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”.
Possible Remedies
Consider the following example:
DECLARE
MYEXCEPTION EXCEPTION;
PRAGMA EXCEPTION_INIT(MYEXCEPTION_1, -20000);
BEGIN
NULL;
END;
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:
DECLARE
MYEXCEPTION EXCEPTION;
PRAGMA EXCEPTION_INIT(MYEXCEPTION, -20000);
BEGIN
NULL;
END;
SSMA will convert the updated code with no errors.
Related Errors
There are several other errors related to Exceptions that you may encounter. These include the following:
· Error O2SS0343 FORALL statement with SAVE EXCEPTION clause is not supported
· O2SS0055 Incorrect EXCEPTION_INIT PRAGMA parameter
References
For more information, check out the following references: