Oracle: Set Query Timeout

Question

Hello, i have a PL/SQL program which do a query to an AS400 database through Transparent Gateway. Sometimes the AS400 not responds to the query (may be network problems )and the PL/SQL program hangs.

Is there any method to set a timeout to the Oracle query so that when certain amount of time passes an exception is risen?

Answer 1

Have you tried setting the HS_FDS_CONNECT_PROPERTIES parameter in the AS400 Transparent Gateway initialisation file?

For a timeout of 2 minutes:

HS_FDS_CONNECT_PROPERTIES="timeout='120'"

Another more general option for setting a query timeout is to create a profile and assign it to the user running your query.

A resource profile can be used to set limits on all sorts of usage in any particular session - one resource limit available is connection time.

For example, you could create a profile as400_tg_profile and assign it a maximum connection time of 2 minutes:

create profile as400_tg_profile limit connect_time 2;

... then you could assign this profile to the user running the query:

alter user as400_tg_user profile as400_tg_profile;

There are lots of options on creating a profile and there are many ways to assign a profile to a particular user so you should read through the documentation.

You could also look into using Oracle Resource Manager creating resource groups and resource profiles if you need to dynamically assign particular resource limits - this gives you fine-grained control of resources for individual sessions.

The Oracle documentation is really good on this - for starters, give this a read:

http://www.oracle.com/technology/products/manageability/database/pdf/twp03/twp_oracle%20database%2010g%20resource%20manager.pdf

For more detail:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#ADMIN027

This is one of those bits of functionality that's easier to use in Enterprise Manager, but a quick PL/SQL example is given in:

http://www.dba-oracle.com/job_scheduling/resource_manager.htm