Re: avoid dynamic SQL
Date: Tue, 30 Jun 2009 11:11:24 +0200 (CEST)
> the formulas are such as
> 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.
JaromirReceived on Tue Jun 30 2009 - 04:11:24 CDT