Re: avoid dynamic SQL

From: Amar Kumar Padhi <amar.padhi_at_gmail.com>
Date: Wed, 1 Jul 2009 09:50:54 +0400 (GST)
Message-ID: <22415497.131246427898856.JavaMail.seven_at_aomfe2p1>

You could consider using Java inside the database for computation jobs also. compile a java program in the database and create a pl/sql wrapper on it. This way you will be using the java pool space. Oracle has a jvm engine running inside the database just like plsql engine (check if you login shows 'with java option' in the banner).

There are some few examples out there on google.

Thanks!
Amar
www.amar-padhi.com

-original message-
 Re: avoid dynamic SQL
From: "amonte" <ax.mount_at_gmail.com>
Date: 30-06-2009 16:01

Guess extenral is the way to go

I have thought about passing the variable to unix bc

Thank you

Alex

2009/6/30 Vladimir Andreev <vandreev_at_gmail.com>

> Hi Alex,
>
> I don't think this can be avoided - you need an engine to calculate these
> formulae. You could try using the SQL engine instead of the PL/SQL engine
> (as in
> open c for 'select '||vFormula||' from dual';
> fetch c into exitcode;
> )
> but that would thrash the shared pool just as efficiently.
>
> The only way I could think of is to do these calculations outside of the
> database: fetch the formula and evaluate it on the client. Or create an
> external procedure and call it from PL/SQL, passing the formula via a bind
> variable.
> http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg11rtn.htm
>
> HTH,
> Flado
>
>
>
> On Tue, Jun 30, 2009 at 10:37, amonte <ax.mount_at_gmail.com> wrote:
>
>> Hi Martin
>>
>> the formulas are such as
>>
>> 1/1+2+3+4+5+6+7
>> 2/5+43+434+33+22
>> 1
>> 2+3+4+4
>>
>>
>> The shared pool is full of BEGIN :EXITCODE statements and is causing
>> ORA-4031 :-[
>>
>> Using 9.2.0.8
>>
>>
>> TIA
>>
>> Alex
>>
>>
>>
>> 2009/6/30 Martin Berger <martin.a.berger_at_gmail.com>
>>
>> Alex,
>>>
>>> Can you give some examples of the formula, please?
>>> what version are you on? In 11g (and 10.2.0.4 with backport and event [1]
>>> ) you can use dbms_shared_pool.purge to purge single sql statements. Maybe
>>> that helps?
>>> Another method I would try is to handle the cursor of your execute
>>> immediate explicite and close it asap afterwards.
>>>
>>> But as I have to confess I does not know what really causes the ORA-4031,
>>> these are all pure suggestions.
>>> Can you check what fills up your SGA?
>>>
>>> best regards,
>>> Martin
>>>
>>> [1]
>>> http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/
>>>
>>>
>>>
>>>
>>> Am 30.06.2009 um 08:42 schrieb amonte:
>>>
>>>
>>> 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
>>>>
>>>>
>>>
>>
>

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

Received on Wed Jul 01 2009 - 00:50:54 CDT