Because the work being done in #2, #4, etc does not use the Oracle connection, the connection is idle and get's dropped by the Oracle server. At some point (say step #7) when you try to use the Oracle connection again, you get a Does this accurately describe your program?

If this is correct, then you should be getting an Oracle error "ORA-02396: exceeded maximum idle time, please connect again"

Assuming that you need to solve this problem in perl only, then there are a couple of options:
1: break your long-running tasks into a loop and periodically use the Oracle connection to reset the IDLE counter. Hopefully you can break your long-running task into a loop over smaller tasks.
2. use threading or forking to allow periodic Oracle connection exercising in parallel with the long-running task. My concern about this approach is that I think perl forking/threading will not work with a single network connection handle to the Oracle session, and that you will be forced to create a new, separate Oracle connection...which is a waste of time because each session will have its own IDLE counter.

Please look into your USER_RESOURCE_LIMITS in Oracle and capture the perl error message from Oracle so that we know what the real problem is here.

You mention that you want all of the postings of information to Oracle to be "one transaction". Presumably you want to do this so that if part of your perl job fails, you can use Oracle's ROLLBACK functionality to undo any uncommited work in the transaction. Transactional work is a cool feature of database software like Oracle, but if you have very long-running processes, you should not use this, because you are holding many Oracle resources (temp talbes, rollback segments) idle for long time periods. In many instances, Oracle may clobber these idle resources, resulting in other errors (ROLLBACK segment too old).

Instead, I would recommend an approach that I have used in the past. I capture the perl script start time and process number and use it as a key in my Oracle log table. I connect to Oracle, add entries to my Oracle log table under my key, then disconnect. I do this repeatedly for all the tasks in my long-running perl job. At the very end of my perl job, I reconnect to Oracle one last time and update the log table, setting a column like UPDATED='Y' for all records under my key. If my perl job had failed, then all my Oracle log table entries would not have had UPDATED='Y' and I would know that the job had failed.

Are there some other requirements you are subject to which prevent you from using this approach?

Thanks much fzellinger

how can I use clone() to resolve this problem.

Unfortunately, that means that the DBD driver would need, possibly extensive modifications. Which unless you are well versed in DBD internals, you should probably leave to the authors.

However, perhaps you can turn things around and put the long running part in a thread, and ping the DB from the main thread like so (also untested):

use threads;
use threads::shared;


$dbh = $conn->databaseHandle();# I am calling this from my connection 
+class

print "dbh: ". $dbh."\n";

my $done :shared = 0;
async {
    # system 'yourLongRunningCmd';
    &long_running_script;
    $done = 1; 
}->detach;

$dbh->ping while !$done and sleep 15; ## Adjust frequency to suit
    
    
print "Thread disappears..Done";
exit;

sub long_running_script {
    print "Testing... long_running test is beign called. will sleep fo
+r 30 sec\n";
    sleep(30);
}
[download]

Thanks much, I used fork insted of threads and it works fine..

Read DBI on threads. Basically, you will need to open a second connection from within your second thread, and then it's up to DBD::Oracle to be threadsafe.

I didn't get that. Would you explain that using an example.I am very new to perl and especially to threads.

What part of the linked documentation do you have problems with? The documentation discusses the problem quite well in my opinion.