Migrating Oracle to SQL Server using SSMA - Error O2SS0055 Incorrect EXCEPTION_INIT PRAGMA parameter
By Bill Ramos and Ankit Matta, 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. Once you know the error code, you can use it with pragma EXCEPTION_INIT and write a handler specifically for that error.
Error O2SS0055 Incorrect EXCEPTION_INIT PRAGMA parameter
Background
Whenever you try to convert PL/SQL code having a “PRAGMA EXCEPTION_INIT” exception without defining an error number in its parameter section, SSMA generates “Error O2SS0055 Incorrect EXCEPTION_INIT PRAGMA parameter” because it doesn’t find a numeric literal. It is mandatory to pass a negative number (i.e. the error number for which you want to throw this exception) in the parameter section.
Possible Remedies
Consider the following example:
DECLARE
MYEXCEPTION EXCEPTION;
PRAGMA EXCEPTION_INIT(MYEXCEPTION, '');
BEGIN
NULL;
END;
When SSMA doesn’t find a numeric literal in the parameter section of PRAGMA EXCEPTION_INIT, SSMA generates the error “Error O2SS0055 Incorrect EXCEPTION_INIT PRAGMA parameter” .
![clip_image001[4]](https://msdntnarchive.z22.web.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/83/metablogapi/4774.clip_image0014_3BCDFF25.jpg "clip_image001[4]")
Remediation of this error is to define an error number for which you want to throw this exception. For this, update the code as follows:
DECLARE
MYEXCEPTION EXCEPTION;
PRAGMA EXCEPTION_INIT(MYEXCEPTION, -10000);
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
· Error O2SS0054 Unable to convert EXCEPTION_INIT
References
For more information, check out the following references: