Home » SQL & PL/SQL » SQL & PL/SQL » How to identify / prevent pl/sql function calls in infinite loop?
How to identify / prevent pl/sql function calls in infinite loop? [message #253731] Tue, 24 July 2007 14:21 Go to next message
Jackaroo
Messages: 2
Registered: July 2007
Junior Member
Dear experts,

I'm looking for an approach to deal with following scenario:

We need to create an interface on our Oracle 10G DB for a third-party application to provide controlled access to some data stored there.

Small and simple solution can be: create two PL/SQL functions in our schema and a new user on DB with only execute grants for these functions. External application uses this user to connect the DB and to retrieve data by calling the pl/sql functions. OK so far, but what if an external application for some reasons (e.g. a bug) tries to call my functions in infinite loop and causes huge resources consumption or even breakdown in my DB?

Is there an approach how to minimize such risks within a pl/sql procedure??

The first idea was to count the calls (per session) within the function, compare the frequency (calls per sec) with a max. allowed value and kill the session or return NULL if a significant risk for the DB performance is identified...

But I have a strong feeling, that this is a very unelegant solution.

What is the best practice to deal with this kind of problems?

Cheers
Werner

Re: How to identify / prevent pl/sql function calls in infinite loop? [message #253735 is a reply to message #253731] Tue, 24 July 2007 14:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>tries to call my functions in infinite loop and causes huge resources consumption
You can't get infinite loops in SQL.

use PROFILES
Re: How to identify / prevent pl/sql function calls in infinite loop? [message #253739 is a reply to message #253735] Tue, 24 July 2007 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
You can't get infinite loops in SQL.

With model clause, you can. Wink
But is this still SQL?

Regards
Michel

[Updated on: Fri, 10 October 2014 01:13]

Report message to a moderator

Re: How to identify / prevent pl/sql function calls in infinite loop? [message #253765 is a reply to message #253735] Tue, 24 July 2007 15:34 Go to previous messageGo to next message
Jackaroo
Messages: 2
Registered: July 2007
Junior Member
anacedent wrote on Tue, 24 July 2007 21:26
>tries to call my functions in infinite loop and causes huge resources consumption
You can't get infinite loops in SQL.


But an external application can call a PL/SQL function in my DB in infinite loop using OCI, ODBC or any other technology. Or just overload the DB by requesting too much information too often.

We want to provide some information to external systems, but on the other hand we have to ensure the availability of our DB...

Regards
Werner
Re: How to identify / prevent pl/sql function calls in infinite loop? [message #253767 is a reply to message #253765] Tue, 24 July 2007 15:38 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create profiles to limit resource consumption.
Create context and/or check application in your PL/SQL to ensure that only your application calls the procedures.
...

Regards
Michel
Previous Topic: error in trigger
Next Topic: Select statement with group by
Goto Forum:
  


Current Time: Fri Dec 09 07:58:45 CST 2016

Total time taken to generate the page: 0.07611 seconds