avoid dynamic SQL

Hi all


I have some problem with some dynamic sql generated by a package, it is
causing ORA-4031 and I have to reboot the instance to get rid of the
problem.

The code does something like

for i in (... cursor ...)
loop
select formula
into vFormula
from calculators
where .......
execute immediate 'BEGIN :EXITCODE:='||vFormula||'; END;';
end loop;

vFormula contains mathematical formulas

The cursor returns around 30000 rows and this is hammering the shared pool
30000 times!

I dont see how can I avoid this dynamic SQL without doing some major changes
(how the formulas are stored for example) and wonder if anyone have some
idea.


TIA


Alex

Other related posts: