When using App Designer, an app is set up with multiple export actions. It is scheduled as a Windows job, and exports snapshot of data to Excel). But the server has a setting that ends a session after 2 hrs. There is no possibility of changing this session limit. The app takes over 2 hr to run all the export actions. Is there a way to set up a check for a lost connection and have the job reconnect if it is lost, so that it can continue the app and exports?
No current check for session/connection.
One app opens one Toad/Oracle session, and runs everything in that one session. Create multiple apps (with multiple exports actions) and schedule each app separately. Multiple Toads will open, meaning multiple session to the database. As long as each app does not take more then session limit set to run all the export actions, then there should be no need to check and reconnect. This would mean multiple scheduled jobs.
There is no option in Toad that check if the session is still open or not. And there is no option to automatically reconnect. An option would be to write individual scripts for the session check, the reconnect if lost, and case handling. There are Control options in the App Designer that can help organize this (if-then-else, while-do, etc), and an option under DB Misc to add a run script option. But this will be based off of individuals' written scripts and estimations on when a disconnect could happen.
As a sample, you can take a look in Help | Contents | Search "While...Do Action". The topic gives an sample of setting up a do-while loop in App Designer. The sample is similar to what you need. While there is a connection, do the actions. But it's not what you need from what I can tell. There is a tns ping check in App Designer, but this does not test the specific session connection. It just tests the listener and if it's available. The services you asked about before is not an option because it works with Windows services, not database sessions. You'd have to set up a loop, have that loop run your own script that tests an open session and reconnection if necessary. You'd need to get the sid of the session yourself. If that script completes, then continue to your exports. You'd also have to script up case handling, i.e. what if you lose connection between exports? There is no way to tell which exports were done or not. Or the situation in which the connection is lost in the middle of an export. There is no way to tell what data was exported or not.