SQL & PL/SQL

Good morning all,
I'm tying to implement something like this:

...
BEGIN
FORALL indx IN 1..vRCollection.COUNT SAVE EXCEPTIONS
EXECUTE IMMEDIATE 'BEGIN PKG.UPDATETABLES@'||dblinkname||' ( :1
,:2 );
END;'
using vRCollection(indx),vCCollection(indx);
EXCEPTION
WHEN others THEN
FOR i IN 1 .. sql%BULK_EXCEPTIONS.COUNT LOOP
WriteException ( pKey1
,vRCollection(sql%BULK_EXCEPTIONS(i).ERROR_INDEX)
,'2'
,vCCollection(sql%BULK_EXCEPTIONS(i).ERROR_INDEX) );
vRejected := vRejected + 1;
END LOOP;
END;
COMMIT;

The idea is to be able to commit the records with correct data and write the ones with dirty data or that generated exceptions into an Exception table.

So I set up my test.
Five Records.
One of them contains a field that is bigger than what one of the tables in PKG.UPDATETABLES is expecting.
I run the procedure and I get one (correct) record in the Exception Table. That is good.
But on the destination tables I can only see two records (expecting four).

So it looks like two records are moved to the destination tables and then the exception occurs and the processing stops.
I was expecting the procedure to continue with the other records.

I then changed the procedure to have a complete block (BEGIN EXCEPTION END) within the statement to be executed by EXECUTE IMMEDIATE.
It did not wok either.

I got an error, the exception within the Dynamic SQL was ignored.

If I use an UPDATE/INSERT statement, the whole thing works...but I need to call PKG.UPDATETABLES .


Am I missing something or is this not possible or supported?

Thanks