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.
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:
· Error O2SS0343 FORALL statement with SAVE EXCEPTION clause is not supported
· O2SS0055 Incorrect EXCEPTION_INIT PRAGMA parameter
For more information, check out the following references:
· Migrating Oracle to SQL Server 2008 White Paper