Re: avoid dynamic SQL
From: Tanel Poder <tanel_at_poderc.com>
Date: Thu, 2 Jul 2009 01:28:20 +0300
Message-ID: <4602f23c0907011528i73a973e5h2744c5d044ae4fc4_at_mail.gmail.com>
Instead of constructing the select string every time and running it with execute immediate, create a function which:
Date: Thu, 2 Jul 2009 01:28:20 +0300
Message-ID: <4602f23c0907011528i73a973e5h2744c5d044ae4fc4_at_mail.gmail.com>
Instead of constructing the select string every time and running it with execute immediate, create a function which:
- takes your expression as a bind variable parameter
- tokenizes this expression into individual pieces
- loops through the tokens and performs appropriate calculation (like CASE when token1 = '+' THEN result := result + token2 ... etc)
- once looped through all tokens, return final result
This all can be done in PL/SQL...
-- Tanel Poder http://blog.tanelpoder.com On Tue, Jun 30, 2009 at 9:42 AM, amonte <ax.mount_at_gmail.com> wrote:Received on Wed Jul 01 2009 - 17:28:20 CDT
> 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
>
>
-- Tanel Poder http://blog.tanelpoder.com -- http://www.freelists.org/webpage/oracle-l