Oracle forms disconnects from server when idle

Using Forms server or connected directly?
Seems there is a timeout parameter.
I have met this with Oracle forms
using Forms server.

I'm not a forms person so I can't speak to specific forms related timeouts.

What's different about those few annoying clients?  I'm thinking Firewall timeout for inactive ports between those few clients.

Slights idea about firewalls sounds realistic!

Actually we have 2 problems:
1 computer is connected directly thru a LAN, this one closes the forms always when computer locks (screen saver pw protection). Once the computer is unlocked, forms close and give the windows error screen.
We also computers connected directly but thru a WAN (remote location w VPN), and there is a PIX 506e in the traffic, all these computers suffer from the error "connection to the database lost" after being idle for some time and has no relation to computer lock, If the user leaves the form open for some time and is doing some other work on the computer).
Could that be the reason for remote users? I never thought of that!

When you loose connection to the database Forms will close.
The user needs new connection.
There was a timeout parameter in the registry that closes the connection after some idle time.

I'll leave the forms expertise to schwertner but I'd definatly get with the network folks about your VPN connections.  I ran into the timeout problem on our internal network when using SQL*Plus.  Our network staff set up an idle disconnection of 2 hours.  My workstation went through a firewall to get to the DB server.  I would get dropped all the time.

For large DML operations, the solution was to remote desktop into the DB server.  This way my SQL*Plus window was running local to the server.

Not sure about the LAN computer.  The screen saver shouldn't interfere with Oracle.

Surprisingly often the change of the Network Card and the cable helps.

This shows the statistics on Experts Exchange.

This is not one computer, these are 5 PCs on the remote site having the same problem.

This is VPN and network problem.
Either lost connection due bad infrastructure and/or timeout in the network infrastructure.

Infrastructure is good as other services are working fine (mail, VoIP, FAXoIP, Intranet).
If it is related to a timeout, what should I check? Is there any setting for timeout in the PIX506?

I'm not 100% sure how VOIP and FAXoIP work but I doubt they need to sustain idle connections for long.  My guess is they open a pipe, use it constantly then close it when finished.

Oracle doesn't do this.  Once you create a connection it creates the pipe.  If the user doesn't do anything in the forms, the connection sits idle.

I'm not a Network person but I do know that you can set the idle timeout in the Firewall.  I had to have our network guys do that for me.  Get with your network folks.  They should be able to help you.

I think there is an easy way to help test this, see if you can get a connection exactly like your remote clients.  Then from a SQL*Plus window do the following:
begin
   exec dbms_lock.sleep(600);
   dbms_output.put_line('all done');
end;
/

This should start a transaction at the database and shouldn't generate any IP traffic between the client and server.  See if you see the output: all done.  If not, your connection has been timed out.

Should I test this command in SQLplus or in Forms?
In SQL it is given error.

I agree with slightwv!
He is correct.

Additionally you have to check what Oracle user/schema the
reports use. Are they same or not?
Please kindly take in account that Oracle users can have profiles that limited the idle connection time and simply terminate the session.

Possibly this doesn't work in Oracle 8/8i, I am not sure.

Please tell us if you use Forms Server (3 tier) or use the old good Client/Server architecture.

>>In SQL it is given error.

My little sleep stub?  This is from SQL*Plus.  My guess is you need to grant execute on DBMS_LOCK to the user.  If this isn't it:  What is the error?

I'm thinking it's not a profile problem.  I would hope the remote users don't have a different profile from the local users.  If they do, schwertner may be correct.

old client server

sqlplus 8.
I granted dbms_lock to the user but still getting the error:
    exec dbms_lock.sleep(600);
         *
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00103: Encountered the symbol "DBMS_LOCK" when expecting one of the following:
:= . ( @ % ;

begin
   execute dbms_lock.sleep(600);
   dbms_output.put_line('all done');
end;
/

 1  begin
  2     execute dbms_lock.sleep(600);
  3     dbms_output.put_line('all done');
  4* end;
SQL> /
   execute dbms_lock.sleep(600);
           *
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00103: Encountered the symbol "DBMS_LOCK" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "DBMS_LOCK" to continue.

This will create the package:

{ORACLE_HOME}/rdbms/admin/dbmslock.sql

run it so:


execute dbms_lock.sleep(600.0);

Yes, this is working on the LAN, I will test it on the WAN today and let you know.

I just noticed this posting today, and I'm responsding partly because we have a similar problem of disconnects with some (but not all) of our Windows client/server Form6i clients connecting to Oracle.  Our problem though only started after we moved our database from Oracle9 on a Windows server to Oracle10.1 on a Linux server.  When we had an Oracle8 database, then later an Oracle9 database on Windows servers (both with the same Forms6i clients that we still use) we never noticed this problem with timeouts.

I will be out on vacation for the next 10 days, but if anyone has any good ideas about this, I hope to find out about them when I get back.

The configuration in this case is
Forms 6 (win XP) and Oracle 8.
I am not sure that Forms 6i is certified to work with Windows XP,
but definitelly it works good with Windows 2000.

Also try to change the network card - may be this can help.
Also may be the hub or the switch these computer are connected
is not good ... enough.

slightwv: Thank you for your SQL.
It ran ok on the remote machine, so I started to increase the time. When dbms_lock.sleep(time) reached 3000, the query failed. So it is a timeout propably in the PIX, because in the LAN, I increased to 6000 and did not disconnect.
Can anyone help how to fix this from the PIX?

Sorry.  I'm not a network person.  You'll need to get with your network folks and negotiate the timeout.  I say negotiate because they will NEVER let it go unlimited.

I'm also not a forms guy so I don't know if this is possible or not but I wanted to throw it out:

If you can have Forms perform an autonomous task without user intervention it should keep the pipe alive.  Something like pulling the sysdate from the DB server every 5 minutes.  This way if the users don't touch any of the forms, at least data is being sent back and forth every 5 minutes.

I am 100% sure this is a PIX issue. I will close the case and repost in the security section a specific question about PIX timeouts.

Thank you for your help.