How to use commit in MERGE statement in oracle 19c?

Question

Is it possible to use a commit statement in MERGE syntax in oracle 19c? I want to perform a commit after every 1000 updates and after every 1000 inserts, how to achieve this?

MERGE INTO target_table 
USING source_table 
ON search_condition
    WHEN MATCHED THEN
        UPDATE SET col1 = value1, col2 = value2,...
        WHERE <update_condition>
        [DELETE WHERE <delete_condition>]
    WHEN NOT MATCHED THEN
        INSERT (col1,col2,...)
        values(value1,value2,...)
        WHERE <insert_condition>;

If the above is not possible, then how would you generally find if 1000 rows are inserted/updated and perform a commit accordingly?

Answer 1

You can't, not within MERGE itself. Commit once, when everything is done.

Besides, why would you want to commit that frequently? What benefit do you expect?

If it MUST be as you described, use a loop and do it in chunks of 1000 rows, then commit. Beware of ORA-01555 which might (or might not) happen when you frequently commit within a loop.