---
title: "O2SS0343: FORALL statement with SAVE EXCEPTIONS clause is not supported (Error)"
description: "Describes why SQL Server Migration Assistant (SSMA) for Oracle does not support the SAVE EXCEPTION clause in the FORALL statement."
author: nahk-ivanov
ms.prod: sql
ms.technology: ssma
ms.devlang: "sql"
ms.topic: "article"
ms.date: "1/22/2020"
ms.author: "alexiva"
---

# O2SS0343: FORALL statement with SAVE EXCEPTION clause is not supported (Error)

This article describes why SQL Server Migration Assistant (SSMA) for Oracle does not support the `SAVE EXCEPTIONS` clause in the `FORALL` statement.

## 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. 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 EXCEPTIONS` clause causes the `FORALL` loop to continue even if some DML operations fail.

The Oracle exception model differs from SQL Server 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 EXCEPTIONS` clause, SSMA doesn't support it and generates an error message.

## Example

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

```sql
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 you try to convert the above code in SSMA, it generates the following error message:

> O2SS0343: FORALL statement with SAVE EXCEPTIONS clause is not supported

## Possible remedies

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:

```sql
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 */
    DECLARE
        @ERRORS int,
        @DML_ERRORS$exception nvarchar(1000)

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

    /* Declaration and initialization of temporary variables */
    DECLARE
        @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
```

## Related conversion messages

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