avoid dynamic SQL

From: amonte <ax.mount_at_gmail.com>
Date: Tue, 30 Jun 2009 08:42:25 +0200
Message-ID: <85c1fb130906292342j4f70b99r2bd0b6173802eb26_at_mail.gmail.com>

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.

TIA Alex

Received on Tue Jun 30 2009 - 01:42:25 CDT

Original text of this message