Incremental commit while using execute immediate when table name and column name are dynamic

Question

I've the need to update a few tables. The tables and columns to be updated will be fetched from another table. So my update statement in the procedure looks like this

EXECUTE IMMEDIATE
        'UPDATE '
     || In_owner
     || '.'
     || In_table_name
     || ' upd_tbl '
     || ' SET '
     || In_sql_stmt_col_list
     || ' WHERE '
     || In_sql_stmt_where_clause;

As you can see, table name, the set clause and the where clause are all dynamically built. What I want to do now is perform a commit after every n records. How do I do that?

Did a google search for you, and what do you know. https://community.oracle.com/thread/937361?tstart=0 — user3224907, Dec 16 '15 at 20:05
Do you mean that for that *single* update statement, you want whatever rows it updates to be committed in batches? Not only is it not possible without rewriting the `update` statement, but it's a bad idea. Why would you want to break the transactionality of the update? — sstan, Dec 16 '15 at 20:23
@user3224907 I know how I would do it for an update statement but I want to know how I can do it when my update is done through a execute immediate. — Swapna Mohan, Dec 16 '15 at 20:54
Simply make `COMMIT;`. `EXECUTE IMMEDIATE 'COMMIT';` will also work, but there is absolutely no need to do it with dynamic SQL — Wernfried Domscheit, Dec 16 '15 at 20:56
@sstan This procedure will be a part of the package which we are building to scramble data being brought from production to development. I need to scramble sensitive information and the database that we will be using to do this won't be a very strong to handle huge updates. Also, we want to be able to run multiple updates in parallel so we are worried about the rollback segments and redo log buffer cache. — Swapna Mohan, Dec 16 '15 at 20:58

score 0 · Answer 1 · answered Dec 17 '15 at 06:10

Assuming that you are able to recognize updated rows from not updated you can try to fit the below sample to your case.

The nbatchsize is number of rows that you want to commit each time.
The i is the number of rows that were updated in the last loop. The updates are looped by while until number of updated rows is lower than your nbatchsize. Of course it would only work when you are able to recognize already updated rows from not updated ones. You can use for that In_sql_stmt_col_list if you don't have any lastupdateon column.

declare 
nbatchsize number := 10;
i number := nbatchsize;
begin


while i >= nbatchsize 
loop 

EXECUTE IMMEDIATE '
update 
Table 
set lastupdateon = sysdate  
where 
lastupdateon < sysdate -1
and rownum <= :1 ' using nbatchsize;

 i := sql%rowcount;

commit;  
dbms_output.put_line(i);

end loop;

end;

Incremental commit while using execute immediate when table name and column name are dynamic

1 Answers1