Re: avoid dynamic SQL
Date: Tue, 30 Jun 2009 10:06:02 +0200
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
 ) 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?
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 ...)
> 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 30 2009 - 03:06:02 CDT
- application/pkcs7-signature attachment: smime.p7s