ROLLBACK inside oracle for loop is not working for the statements mentioned outside of for loop

Question

I have a pl sql procedure which accepts array of elements and inserts them into a table.

At the beginning of the procedure i am deleting data from backup table and inserting data from main table into backup table. Then i am deleting data from main table and looping through the arguments to the proc and inserting records. When i face dup_val_on_index exception, the rollback is happening to start point of the proc. I mean the exception block is getting executed. But the rollback is not happening.

For example, if i insert 2 rows which has duplicate values, dup_val_on_index exception has to be raised and 1st row should not be inserted.

Below is my code. If any exception happening inside the loop, i want to rollback the insert as well and delete operation performed at the beginning of the procedure

PROCEDURE insert_sales_data (
    p_depot_code       IN depotcode_array,
    p_depot_name       IN depotname_array,
    p_dell_split       IN dellsplit_array,
    p_sector           IN sector_array,
    p_locality         IN locality_array,
    p_tnt_depot_code   IN tntdepotcode_array,
    p_postal_code      IN postalcode_array,
    p_primary_sort     IN primarysort_array,
    p_secondary_sort   IN secondarysort_array,
    p_user             IN VARCHAR2,
    p_error_message    OUT VARCHAR2,
    p_count            OUT NUMBER
)
    IS
BEGIN
    SAVEPOINT s1;
    DELETE FROM sales_backup;

    INSERT INTO sales_backup
        SELECT
            *
        FROM
            sales;

    DELETE FROM sales;

    FOR i IN p_sector.first..p_sector.last LOOP
        BEGIN
            INSERT INTO sales (
                depot_code,
                depot_name,
                dell_split,
                sector,
                locality,
                tnt_depot_code,
                postal_code,
                primary_sort,
                secondary_sort,
                create_date,
                create_user_id,
                uuid
            ) VALUES (
                p_depot_code(i),
                p_depot_name(i),
                p_dell_split(i),
                p_sector(i),
                p_locality(i),
                p_tnt_depot_code(i),
                p_postal_code(i),
                p_primary_sort(i),
                p_secondary_sort(i),
                SYSDATE,
                p_user,
                sys_guid()
            );

        EXCEPTION
            WHEN dup_val_on_index THEN
                ROLLBACK TO s1;
                EXIT;
            WHEN OTHERS THEN
                ROLLBACK TO s1;
                EXIT;
        END;
    END LOOP;

    SELECT
        COUNT(*)
    INTO p_count
    FROM
        uk_depots;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO s1;
END;

"If any exception happening inside the loop, i want to rollback the insert as well and delete operation performed at the beginning of the procedure" - that's what this will do. When it rolls back to s1 it will revert the delete and insert at the start, and anything inserted in the loop. It isn't clear what problem you are having. [An MCVE might help](https://stackoverflow.com/help/mcve), but please edit your question to explain what is not behaving as you want/expect. Also you might find it useful to at least display the errors that are being caught. — Alex Poole, Jul 16 '18 at 14:16
I would suggest you to rewrite your procedure and avoid all those unnecessary array parameters and pass a single refcursor parameter instead, from the calling program( that too only if required). Preferably run that `insert` operation as single statement rather than using those cursor loops. — Kaushik Nayak, Jul 16 '18 at 14:22

score 0 · Answer 1 · answered Jul 16 '18 at 14:23

Hoping, I understood your question correctly. Please try to use below block.

    PROCEDURE insert_sales_data (
        p_depot_code       IN depotcode_array,
        p_depot_name       IN depotname_array,
        p_dell_split       IN dellsplit_array,
        p_sector           IN sector_array,
        p_locality         IN locality_array,
        p_tnt_depot_code   IN tntdepotcode_array,
        p_postal_code      IN postalcode_array,
        p_primary_sort     IN primarysort_array,
        p_secondary_sort   IN secondarysort_array,
        p_user             IN VARCHAR2,
        p_error_message    OUT VARCHAR2,
        p_count            OUT NUMBER
    )
        IS
    BEGIN

        DELETE FROM sales_backup;

        INSERT INTO sales_backup
            SELECT
                *
            FROM
                sales;

        DELETE FROM sales;

        FOR i IN p_sector.first..p_sector.last LOOP
            BEGIN
                INSERT INTO sales (
                    depot_code,
                    depot_name,
                    dell_split,
                    sector,
                    locality,
                    tnt_depot_code,
                    postal_code,
                    primary_sort,
                    secondary_sort,
                    create_date,
                    create_user_id,
                    uuid
                ) VALUES (
                    p_depot_code(i),
                    p_depot_name(i),
                    p_dell_split(i),
                    p_sector(i),
                    p_locality(i),
                    p_tnt_depot_code(i),
                    p_postal_code(i),
                    p_primary_sort(i),
                    p_secondary_sort(i),
                    SYSDATE,
                    p_user,
                    sys_guid()
                );

            EXCEPTION
                WHEN dup_val_on_index THEN
                    ROLLBACK;
                    EXIT;
                WHEN OTHERS THEN
                    ROLLBACK;
                    EXIT;
            END;
        END LOOP;

        SELECT
            COUNT(*)
        INTO p_count
        FROM
            uk_depots;

    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
    END;

This will roll back the entire current transactions; the OP was setting up and rolling back to a savepoint. — Alex Poole, Jul 16 '18 at 14:29
Maybe, this link can help you. https://stackoverflow.com/questions/23464492/how-to-use-savepoints-in-oracle-procedure — Tajinder, Jul 16 '18 at 14:33
Yes, as in your procedure savepoint is immediately after the begin, means you to need rollback completely. That's why i removed the savepoint and used only rollback. Please correct me if i am getting you wrong. — Tajinder, Jul 16 '18 at 14:39
It isn't my procedure *8-) Why do you think you need to rollback completely though? You could do some other DML before calling this procedure, and still want to rollback just to the start of the procedure - which is what the savepoint is for. What is wrong with having the savepoint as the first thing after `begin`? — Alex Poole, Jul 16 '18 at 15:44
i verified with savepoint and without. but still i have the same issue. — Rajesh Kannah, Jul 16 '18 at 15:51

score 0 · Answer 2 · answered Jul 16 '18 at 15:20

From how I understood your question it should be simpler:

PROCEDURE insert_sales_data (
    p_depot_code       IN depotcode_array,
    p_depot_name       IN depotname_array,
    p_dell_split       IN dellsplit_array,
    p_sector           IN sector_array,
    p_locality         IN locality_array,
    p_tnt_depot_code   IN tntdepotcode_array,
    p_postal_code      IN postalcode_array,
    p_primary_sort     IN primarysort_array,
    p_secondary_sort   IN secondarysort_array,
    p_user             IN VARCHAR2,
    p_error_message    OUT VARCHAR2,
    p_count            OUT NUMBER
)
    IS
BEGIN
    SAVEPOINT s1;
    DELETE FROM sales_backup;

    INSERT INTO sales_backup
        SELECT
            *
        FROM
            sales;

    DELETE FROM sales;

    FOR i IN p_sector.first..p_sector.last LOOP
            INSERT INTO sales (
                depot_code,
                depot_name,
                dell_split,
                sector,
                locality,
                tnt_depot_code,
                postal_code,
                primary_sort,
                secondary_sort,
                create_date,
                create_user_id,
                uuid
            ) VALUES (
                p_depot_code(i),
                p_depot_name(i),
                p_dell_split(i),
                p_sector(i),
                p_locality(i),
                p_tnt_depot_code(i),
                p_postal_code(i),
                p_primary_sort(i),
                p_secondary_sort(i),
                SYSDATE,
                p_user,
                sys_guid()
            );

    END LOOP;

    SELECT
        COUNT(*)
    INTO p_count
    FROM
        uk_depots;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO s1;
END;

This block is fairly useless, you perform the rollback no matter which error you get.

    EXCEPTION
        WHEN dup_val_on_index THEN
            ROLLBACK TO s1;
            EXIT;
        WHEN OTHERS THEN
            ROLLBACK TO s1;
            EXIT;
    END;

on the first hand i wrote the code without begin end inside the loop. Even then the same issue was present. so i thought it would be good i can write a block inside loop and catch any issues in the insert. but that doesnt work. The issue here is i could catch the dup_val_on_index . but the rollback statement in the exception block is not reverting the changes. — Rajesh Kannah, Jul 16 '18 at 16:00

ROLLBACK inside oracle for loop is not working for the statements mentioned outside of for loop

2 Answers2