Re: avoid dynamic SQL
Date: Wed, 1 Jul 2009 16:16:24 +1000
begin execute immediate 'alter session set cursor_sharing=''FORCE'''; end; /
Would be a quick fix. In your case.
On Tue, Jun 30, 2009 at 4:42 PM, amonte<ax.mount_at_gmail.com> wrote:
> 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
> 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
-- Jurijs +371 29268222 (+2 GMT) ============================================ http://otn.oracle.com/ocm/jvelikanovs.html -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 01 2009 - 01:16:24 CDT