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
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 ...)
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-lReceived on Tue Jun 30 2009 - 01:42:25 CDT