Have you considered using CONTEXT variables? On Wed, Jul 1, 2009 at 6:45 AM, Edgar Chupit <chupit@xxxxxxxxx> 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@xxxxxxxxx> wrote: > > I have 500MB Shared Pool and the instance dies once every 3 to 4 weeks > due > > to 4031 > > > > Alex > > > -- > //www.freelists.org/webpage/oracle-l > > >