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



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

References:

avoid dynamic SQL
From: amonte

Re: avoid dynamic SQL
From: Martin Berger

Re: avoid dynamic SQL
From: amonte

Re: avoid dynamic SQL

Other related posts: