By Bill Ramos and Mayank Bhanawat, Advaiya Inc.

This blog post describes why SQL Server Migration Assistant (SSMA) for Oracle doesn’t support the SAVE EXCEPTION clause in the FORALL statement. 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.  In Oracle, FORALL statement lets you run multiple DML statements very efficiently and can only repeat a single DML statement, unlike a general-purpose FOR loop. SAVE Exception statement is an optional keywords that cause the FORALL loop to continue even if some DML operations fail.

Error O2SS0343 FORALL statement with SAVE EXCEPTION clause is not supported

Background

The Oracle exception model differs from Microsoft SQL Server 2008 both in exception raising and exception handling. It is preferable to use the SQL Server exceptions model as part of the Oracle PL/SQL code migration.

Whenever FORALL statement used with SAVE exception clause, SSMA doesn’t support it and generate following error message: Error O2SS0343 FORALL statement with SAVE EXCEPTION clause is not supported.

Possible Remedies

Consider the example below which uses a FORALL statement with SAVE EXCEPTION clause.

CREATE TABLE DIVISION_RESULT_Exception (RESULT NUMBER);

/

DECLARE

  TYPE NUMLIST IS TABLE OF NUMBER;

  NUM_TAB NUMLIST := NUMLIST(1000,0,100,0,10);

  ERRORS NUMBER;

  DML_ERRORS EXCEPTION;

  PRAGMA EXCEPTION_INIT(DML_ERRORS, -24381);

BEGIN

  FORALL i IN NUM_TAB.FIRST..NUM_TAB.LAST SAVE EXCEPTIONS

     INSERT INTO DIVISION_RESULT_Exception VALUES(1000/NUM_TAB(i));

EXCEPTION

WHEN DML_ERRORS THEN

  ERRORS := SQL%BULK_EXCEPTIONS.COUNT;

  DBMS_OUTPUT.PUT_LINE('Number of errors is ' || ERRORS);

  FOR i IN 1..ERRORS LOOP

     DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);

     DBMS_OUTPUT.PUT_LINE('SQLERRM: ' ||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

  END LOOP;

END;

 

 

When SSMA tries to convert the above code, it generates following error: “Error O2SS0343 FORALL statement with SAVE EXCEPTION clause is not supported”.

 

Error O2SS0343

One possible remediation is to use the try and catch block to handle the exceptions in T-SQL using ERROR_NUMBER and ERROR_MESSAGE functions instead of the SQLCODE and SQLERRM Oracle functions. For this, we need to update the SQL Server code as follows:

BEGIN

/*Declaration and initialization of table of input values*/

   DECLARE

      @CollectionIndexInt$TYPE varchar(max) = ' TABLE OF DOUBLE'

 

   DECLARE

      @NUM_TAB dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE).AddDouble(1000).AddDouble(0).AddDouble(100).AddDouble(0).AddDouble(10),

/* Declaration and initialization of other variables*/

      @ERRORS int,

      @DML_ERRORS$exception nvarchar(1000)

        SET @DML_ERRORS$exception = N'ORA-24381%'

 

   DECLARE

/* Declaration and initialization of temporary variables */

      @i int

      SET @i = 1

     

/* Running the loop for all the input values*/

      WHILE @i <= @NUM_TAB.Count

  

      BEGIN

/* Performing the required operation in Try block */

        BEGIN TRY

            INSERT dbo.DIVISION_RESULT_EXCEPTION(RESULT)

            VALUES (1000 / @NUM_TAB.GetDouble(@i))

        END TRY

/* Catch block to handle exception generated in Try block */

        BEGIN CATCH

           set  @Errors = @Errors + 1;

          Print ('SQL error is ' + convert(varchar(20),Error_Number()) + ':' + convert(varchar(100),ERROR_MESSAGE()) )

           Print( Convert(Varchar(30),(Error_Number())))

        END CATCH;

/*Incrementing the loop variable*/          

  SET @i = @i + 1

      END

END

GO

 

Related Errors

There are several other errors related to “Exception Handling” that you may encounter. These include the following:

·         Error O2SS0282: RAISE without exception specified can be placed only in exception handler

 

References

For more information, check out the following references:

·         Migrating Oracle to SQL Server 2008 White Paper