Hi!

In VFP 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 one SQLECEC() command from VFP, run some commands, then finish transaction, not doing everything in a single script? Can such approach help if it is available?

Thanks in advance.