Re: avoid dynamic SQL

> 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 :-[
>

You may try to limit the nuber of the dynamic SQL's using a bulk evaluation. Something like this (pseudocode)

EXECUTE IMMEDIATE 'insert into result
select 'formula1' , 1/1+2+3+4+5+6+7 from dual union all 'formula2' , 2/5+43+434+33+22 from dual union all ...

You may limit the number of cursors significantly generating e.g. 100 formulas in one statement.
The downside is that the error handling is a bit complicated.

Of course an other possibility is to calculate the results out of the database using some language that support eval natively.

regards,

Jaromir

--
http://www.freelists.org/webpage/oracle-l