Consulting a fellow teachers, I have thousands of update statement, in the begin... In the end of the block, execution efficiency is bottom, don't know what good method?
The update are single table updates, such as update aa set x=y where id=z so simple, I tested puts 6000 lines of the update statement block, after seven minutes, 3000 to 1 minute, I don't know how put block statement is the most reasonable, put 10000 ORACLE directly gave me an error, error is as follows: ORA - 04030, in an attempt to allocate 4088 bytes (PLS CGA HP. PdzgM64_New_Link) process of memory,

CodePudding user response:

Use cursor plus partial updated forall

CodePudding user response:

If is the update aa set x=y where id=z, can consider to change to bind variables form, see if I can regular replacement,

Declare
V_sql varchar2 (1000);
The begin
V_sql:='update aa set x=: where id=1:2'.
The execute immediate v_sql using "CCC", 100;
The execute immediate v_sql using 'DDD, 200;
end;