Re: avoid dynamic SQL

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Wed, 1 Jul 2009 07:50:15 -0700
Message-ID: <26fdee6e0907010750j2d6a362fl906733c87bd4d133_at_mail.gmail.com>

Have you considered using CONTEXT variables?

On Wed, Jul 1, 2009 at 6:45 AM, Edgar Chupit <chupit_at_gmail.com> wrote:

> Dear Alex,
>
> If you really want to save some memory and don't afraid of Java (I
> know that some people afraid of Java in DB)... you can use XMLQuery to
> do expression evaluation. It can get tricky if you want to mix
> datatypes (you have to use special syntax for that), etc, but it works
> great and does not cause additional load on shared pool, because you
> can pass expression as bind variable.
>
> This works also in 10g:
>
> select XMLQuery('1+3*5-1' returning content).getnumberval() result
> from dual
>
> SQL> /
> RESULT
> ----------
> 15
>
> Unfortunately it does not work perfectly in 10g and you can not use
> bind variables (you will get ora-19102), but in 11g you can even use
> bind variables, and get something like this:
>
> SQL> alter session set sql_trace = true;
> Session altered.
>
> SQL> var exp varchar2(100);
>
> SQL> exec :exp := '1+3*4-10';
> PL/SQL procedure successfully completed.
>
> SQL> select XMLQuery(:exp returning content).getnumberval() result
> 2 from dual
> 3 /
> RESULT
> ----------
> 3
>
> SQL> exec :exp := '1+3*11-15';
> PL/SQL procedure successfully completed.
>
> SQL> select XMLQuery(:exp returning content).getnumberval() result
> 2 from dual
> 3 /
> RESULT
> ----------
> 19
>
> select XMLQuery(:exp returning content).getnumberval() result
> from dual
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 2 0.00 0.00 0 0 0
> 0
> Execute 2 0.00 0.00 0 0 0
> 0
> Fetch 4 0.03 0.00 0 6 24
> 2
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 8 0.03 0.00 0 6 24
> 2
>
> Misses in library cache during parse: 1
> Optimizer mode: ALL_ROWS
> Parsing user id: SYS
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
>
> Hope it can help you.
>
> Best regards,
> Edgar Chupit
> callto://edgar.chupit
>
>
>
> On Wed, Jul 1, 2009 at 11:05 AM, amonte<ax.mount_at_gmail.com> wrote:
> > I have 500MB Shared Pool and the instance dies once every 3 to 4 weeks
> due
> > to 4031
> >
> > Alex
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 01 2009 - 09:50:15 CDT