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
orROLLBACK
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;