Re: avoid dynamic SQL
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <ax.mount@xxxxxxxxx>
- Date: Tue, 30 Jun 2009 11:11:24 +0200 (CEST)
Hi Alex,
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
