Instead of constructing the select string every time and running it with execute immediate, create a function which: 1) takes your expression as a bind variable parameter 2) tokenizes this expression into individual pieces 3) loops through the tokens and performs appropriate calculation (like CASE when token1 = '+' THEN result := result + token2 ... etc) 4) once looped through all tokens, return final result This all can be done in PL/SQL... -- Tanel Poder http://blog.tanelpoder.com On Tue, Jun 30, 2009 at 9:42 AM, 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 > > -- Tanel Poder http://blog.tanelpoder.com