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.

Error O2SS0054_01

 

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:

·         Migrating Oracle to SQL Server 2008 White Paper