Oracle pl/sql: executing dynamic delete within a transaction

Question

I need to delete one or more row from list of tables stored in a table, and commit only if all deletion succeed. So I wrote something like this (as part of a bigger procedure):

BEGIN
    SAVEPOINT sp;

    FOR cur_table IN (SELECT * FROM TABLE_OF_TABLES)
    LOOP
        EXECUTE IMMEDIATE 'DELETE FROM ' || cur_table.TABNAME || ' WHERE ID = :id_bind'
        USING id;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT sp;
END;

I know this couldn't work, because of the "execute immediate".

So, what is the correct way to do that?

what is the issue? are you having an error, a wrong result, ... ? — Aleksej, Jul 27 '18 at 09:41
I haven't tried yet. I guess "execute immediate" commit itself. Is that wrong? — MonkeyH, Jul 27 '18 at 09:50
`id` doesn't seem to be defined anywhere. Also, an anonymous block like this will roll back on failure anyway, so you don't need the savepoint and exception handler. — William Robertson, Jul 27 '18 at 10:31
Execute immediate does not automatically commit. It's common misconception, because sometimes you use 'execute immediate' with DDL statements - but then it is DDL part that actually does commit. — piezol, Jul 27 '18 at 11:53

score 0 · Answer 1 · answered Jul 29 '18 at 03:57

Honestly, this sounds like a bad idea. Since you have the tables stored in the database, why not just list them out in your procedure? Surely, you're not adding tables that often that this procedure would need to be updated often?

As pointed out in the comments, this will work fine, since EXECUTE IMMEDIATE does not automatically commit.

Don't forget to add a RAISE at the end of your exception block, or you'll never know that an error happened.

score 0 · Accepted Answer · answered Aug 31 '18 at 07:29

0

Dynamic SQL (Execute Immediate) doesn't commit the transaction. You have to commit explicitly. Your code is fine but it doesn't record/log the errors in case if they occur. Log the errors in the exception handler section.

answered Aug 31 '18 at 07:29

Jagadeesh Sali

34
4

Oracle pl/sql: executing dynamic delete within a transaction

2 Answers2