Hi Alex, I don't think this can be avoided - you need an engine to calculate these formulae. You could try using the SQL engine instead of the PL/SQL engine (as in open c for 'select '||vFormula||' from dual'; fetch c into exitcode; ) but that would thrash the shared pool just as efficiently. The only way I could think of is to do these calculations outside of the database: fetch the formula and evaluate it on the client. Or create an external procedure and call it from PL/SQL, passing the formula via a bind variable. http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg11rtn.htm HTH, Flado On Tue, Jun 30, 2009 at 10:37, amonte <ax.mount@xxxxxxxxx> wrote: > Hi Martin > > the formulas are such as > > 1/1+2+3+4+5+6+7 > 2/5+43+434+33+22 > 1 > 2+3+4+4 > > > The shared pool is full of BEGIN :EXITCODE statements and is causing > ORA-4031 :-[ > > Using 9.2.0.8 > > > TIA > > Alex > > > > 2009/6/30 Martin Berger <martin.a.berger@xxxxxxxxx> > > Alex, >> >> Can you give some examples of the formula, please? >> what version are you on? In 11g (and 10.2.0.4 with backport and event [1] >> ) you can use dbms_shared_pool.purge to purge single sql statements. Maybe >> that helps? >> Another method I would try is to handle the cursor of your execute >> immediate explicite and close it asap afterwards. >> >> But as I have to confess I does not know what really causes the ORA-4031, >> these are all pure suggestions. >> Can you check what fills up your SGA? >> >> best regards, >> Martin >> >> [1] >> http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/ >> >> >> >> >> Am 30.06.2009 um 08:42 schrieb amonte: >> >> >> 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 >>> >>> >> >