Transaction block is too large

[TomasDill]

Hi!

We're creating a PL/SQL script to update 100 tables in a single transaction. As soon as we try to execute such script, we get an error:

>>>>>>>>
"PLS-00123 program too large

Cause: PL/SQL was designed primarily for robust transaction processing.
One consequence of the special-purpose design is that the PL/SQL compiler
imposes a limit on block size.
The limit depends on the mix of statements in the PL/SQL block.
Blocks that exceed the limit cause this error.

Action: The best solution is to modularize the program by defining subprograms,
which can be stored in an Oracle database.
Another solution is to break the program into two sub-blocks.
Have the first block INSERT any data the second block needs into
a temporary database table. Then, have the second block SELECT the data from the table. "
>>>>>>>>

We cannot split this because we really neeed to update these 100 tables in a single transaction, when we fail, say at updating of 78 table, we need to rollback changes for all tables.

- What would be a good solution in such case?
- Is there a way to increase the Oracle limitation for transaction blocks?
- Are there way to start transaction by a separate command for ODBC connection session, run some commands, then finish transaction - not doing everything in a single script? Can such approach help if it is available?

Thanks in advance. Vlad Grynchyshyn, MVP
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[sem]

They propose modularize the program, this does not affect transaction processing: just add some begin/end
begin
update tab1..
end;
/
begin
update tab2..
end;
/

instead of

begin
update tab1..
update tab2..
end;
/