RE: avoid dynamic SQL
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <ax.mount@xxxxxxxxx>, "Oracle-L Group" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 Jun 2009 09:20:35 -0400
Alex,
What is your current shared pool size? Also, is it possible for
your developer to close/rollback his efforts so that the space can be
reclaimed. Say:
for i in (... cursor ...)
loop
select formula
into vFormula
from calculators
where .......
execute immediate 'BEGIN :EXITCODE:='||vFormula||'; Rollback; END;';
end loop;
Dick Goulet
Senior Oracle DBA
PAREXEL International
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of amonte
Sent: Tuesday, June 30, 2009 2:42 AM
To: Oracle-L Group
Subject: avoid dynamic SQL
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
Other related posts:
