Re: avoid dynamic SQL

I don't think this can be avoided - you need an engine to calculate these formulae. You could try using the SQL engine instead of the PL/SQL engine (as in
open c for 'select '||vFormula||' from dual'; fetch c into exitcode;
)
but that would thrash the shared pool just as efficiently.

The only way I could think of is to do these calculations outside of the database: fetch the formula and evaluate it on the client. Or create an external procedure and call it from PL/SQL, passing the formula via a bind variable.
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg11rtn.htm

HTH,
Flado

On Tue, Jun 30, 2009 at 10:37, amonte <ax.mount_at_gmail.com> wrote:

> Hi Martin
>
> 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 :-[
>
> Using 9.2.0.8
>
>
> TIA
>
> Alex
>
>
>
> 2009/6/30 Martin Berger <martin.a.berger_at_gmail.com>
>
> Alex,
>>
>> Can you give some examples of the formula, please?
>> what version are you on? In 11g (and 10.2.0.4 with backport and event [1]
>> ) you can use dbms_shared_pool.purge to purge single sql statements. Maybe
>> that helps?
>> Another method I would try is to handle the cursor of your execute
>> immediate explicite and close it asap afterwards.
>>
>> But as I have to confess I does not know what really causes the ORA-4031,
>> these are all pure suggestions.
>> Can you check what fills up your SGA?
>>
>> best regards,
>> Martin
>>
>> [1]
>> http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/
>>
>>
>>
>>
>> Am 30.06.2009 um 08:42 schrieb amonte:
>>
>>
>> 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
>>>
>>>
>>
>

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