Edit

Share via

O2SS0055: Incorrect EXCEPTION_INIT PRAGMA parameter (Error)

This article covers the reason why SQL Server Migration Assistant (SSMA) for Oracle can't convert EXCEPTION_INIT.

Background

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. You can use the PRAGMA EXCEPTION_INIT to associate exception names with other Oracle error codes that you can anticipate. Once you know the error code, you can use it with PRAGMA EXCEPTION_INIT and write a handler specifically for that error.

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 an error message because it doesn't find a numeric literal. It's mandatory to pass a negative number (that is, the error number for which you want to throw this exception) in the parameter section.

Example

Consider the following example:

DECLARE
    MYEXCEPTION EXCEPTION;
    PRAGMA EXCEPTION_INIT(MYEXCEPTION, '');
BEGIN
    NULL;
END;

When you try to convert the previous code in SSMA, it doesn't find a numeric literal in the parameter section of PRAGMA EXCEPTION_INIT and hence generates the following error message:

O2SS0055: Incorrect EXCEPTION_INIT PRAGMA parameter

Possible remedies

Remediation of this error is to define an error number for which you want to throw this exception. Update the code as follows:

DECLARE
    MYEXCEPTION EXCEPTION;
    PRAGMA EXCEPTION_INIT(MYEXCEPTION, -10000);
BEGIN
    NULL;
END;