Search code examples
oracle-databaseplsqldynamic-sqlexecute-immediate

Oracle pl/sql: executing dynamic delete within a transaction

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?

Solution

  • 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.