Re: avoid dynamic SQL

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