Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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;