Edit

Share via

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

This article describes why SQL Server Migration Assistant (SSMA) for Oracle doesn't 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 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's 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

In the following example, you use a FORALL statement with SAVE EXCEPTIONS 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 you try to convert the previous 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.

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 */
    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
  • O2SS0282: RAISE without exception specified can be placed only in exception handler