isolation level serializable

Oracle 9.2.0.5.

I have a stored procedure that copies a bunch of tables from one schema to another using "execute immediate 'insert...' " and code that looks something like this...

for i in (select table_name from ....)
loop
  stmt := 'insert into s1.'||table_name;   stmt := stmt||' (select * from s2.'||table_name')';   execute immediate stmt;
end loop;

Some of the tables have FK relationships between them on both the source and destination schemas. The procedure uses a similar loop early on to disable all constraints on the destination tables, and another later to enable them again after the inserts are done. The constraints stay enabled the entire time on the source tables.

The problem is that I am getting FK constraint violations when I try to enable the constraints on the destination tables. I thought I could eliminate this by using a serializable isolation level for the transaction. According to the manual, this will cause all queries to look at a snapshot of the source tables from the same point in time, so I execute this before the first insert.

commit;
set transaction isolation level serializable;

Then I do another commit at the end of the last insert. There are no commits, rollbacks, DDL, or any other SQL between the inserts, however I still get FK violations at the end when I try enable the constraints. Why? Am I misunderstanding the serializable isolation level? Is there something special about "execute immediate" where it ignores the serializable transaction setting?

TIA Received on Wed Mar 21 2007 - 09:07:30 CDT