Re: avoid dynamic SQL
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: j.velikanovs@xxxxxxxxx
- Date: Wed, 1 Jul 2009 11:02:03 +0200
That wont work because his formulas are not really variables which can be
bind. It will if he uses them in SQL predicates
Thanks
--
LSC
On Wed, Jul 1, 2009 at 8:16 AM, Jurijs Velikanovs <j.velikanovs@xxxxxxxxx>wrote:
> begin execute immediate 'alter session set cursor_sharing=''FORCE'''; end;
> /
>
> Would be a quick fix. In your case.
>
> Yury
>
> On Tue, Jun 30, 2009 at 4:42 PM, amonte<ax.mount@xxxxxxxxx> 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
> > 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
> >
> >
>
>
>
> --
> Jurijs
> +371 29268222 (+2 GMT)
> ============================================
> http://otn.oracle.com/ocm/jvelikanovs.html
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
