Differences in TimesTen: Transaction Behavior

In TimesTen, any operation that ends your transaction closes all cursors associated with the connection.

This includes the following:

• Any COMMIT or ROLLBACK statement

• Any DDL statement

For example, consider the following scenario, where you want to recompile a set of procedures. This would not work, because the first time ALTER PROCEDURE is executed, the cursor (pnamecurs) would be closed:

declare
   cursor pnamecurs is select * from all_objects where object_name like 'MYPROC%';
begin
   for rec in pnamecurs loop
     execute immediate 'alter procedure ' || rec.object_name || ' compile';
   end loop;
end;

Instead, you can do something like the following, which fetches all the procedure names into an internal table then executes ALTER PROCEDURE on them with no active cursor.

declare
   cursor pnamecurs is select * from all_objects where object_name like 'MYPROC%';
   type tbl is table of c%rowtype index by binary_integer;
   myprocs tbl;

begin
   open pnamecurs;
   fetch pnamecurs bulk collect into myprocs;
   close pnamecurs;
   for i in 1..myprocs.count loop
     execute immediate 'alter procedure ' || myprocs(i).object_name || ' compile';
   end loop;
end;