Oracle Database Discussions

I have been developing some PL/SQL procedures on a 10g express database (containing a small sample of data). The procedures will be used in a one off migration exercise.

To run and test them, I have a SQL script that executes them in the correct sequence. I run this 'control' script in SQLPlus. This has worked fine in development.

Last week I had access to a full size copy of the production database that they should run on, for the first time. I tried to run the script from SQLPlus again and watched their progress through a log table that I have created and that is written to, during the execution of the procedures.

To my horror, the procedures stopped after about 100 minutes of processing. I understand that this is due to inactivity in the SQLPlus window. The inactivity causes the session to timeout, and when the session timeouts, the procedure running is cancelled.

I am investigating using DBMS_JOBS (although developing on a 10g, I will be connecting SQLPlus to a 9.2 to run in production so not sure whether JOB_SCHEDULER is suitable), but in the meantime thought I'd throw out a simple question to the forum.

Is there a way that I can either override the timeout happening in the SQLPlus session, or, alternatively, is there a way that I can force the job to run in another session other than the SQLPlus one?

Looking forward to some constructive suggestions...

Thanks
Chloe