begin execute immediate 'alter session set cursor_sharing=''FORCE'''; end; / Would be a quick fix. In your case. Yury On Tue, Jun 30, 2009 at 4:42 PM, amonte<ax.mount@xxxxxxxxx> wrote: > 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 > > -- Jurijs +371 29268222 (+2 GMT) ============================================ http://otn.oracle.com/ocm/jvelikanovs.html -- //www.freelists.org/webpage/oracle-l