Hi Vlad,

In VFP, you may try to :
1. using VFP update view:
USE TEST1 IN 0
USE TEST2 IN 0 ...
CURSORSETPROP('Buffering',5,'TEST1') .....

BEGIN TRANSACTION
INSERT INTO TEST1 VALUES ('TEST1')
INSERT INTO TEST2 VALUES ('TEST2')

IF lError
ROLLBACK
ELSE
TABLEUPDATE(2,.T.,'TEST1')
TABLEUPDATE(2,.T.,'TEST2')
END TRANSACTION
ENDIF

2. using SQL pass-through
n=SQLCONNECT('test')
** set manual transaction
SQLSETPROP(n,'Transactions',2)

FOR I = 1 TO 100
sValue = 'TESTING2_'+LTRIM(STR(i))
nErr = SQLEXEC(n,"INSERT INTO VFP_TEST+LTRIM(STR(I))"+" VALUES ('"+sValue+"')")
IF nErr < 0
lError = .T.
ELSE ....
NEXT
IF lError
SQLROLLBACK(n)
ELSE
SQLCOMMIT(n)
ENDIF

In Oracle, I think you may try to adjust the value of SHARED_SPOOL_SIZE and
DB_BLOCK_BUFFERS.

Hope it helps you.

>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.