DBI - DBD:Oracle problem?

eugemz has asked for the wisdom of the Perl Monks concerning the following question:

I need help! I can not even count the number of hours that I use to find a solution for this problem! I’m just giving up.

I have a perl program (#1) that process text files and insert the data in a DB. In this case is running in linux ubuntu and Oracle 10.g DB. I use DBD::Oracle Version 1.21 and DBI Version 1.604. Oracle Instant Client Version 10.2.0.4

As there could be thousand of text files, I have a perl program (#2) that allocate the text files based on the size in X folders and call X times perl program (#1) to process the files that where distributed in the folders.

The perl program (#2) runs well until any of the perl program instances (#1) finish. When the instance finish and get back to the program that called it, I loose the connection with the DB and I get the error

DBD::Oracle::db prepare failed: Error while trying to retrieve text for error ORA-03113 (DBD ERROR: OCIStmtExecute/Describe)

I trace the DBI connection and I get this message,

   <- DESTROY(DBI::st=HASH(84d52b4))= undef
    <- disconnect_all= (not implemented) at DBI.pm line 716
!   <- DESTROY(DBI::db=HASH(84e2148))= undef during global destruction
!   <- DESTROY(DBI::dr=HASH(84d5590))= undef during global destruction
    !! ERROR: '3113' 'Error while trying to retrieve text for error OR
+A-03113 (DBD ERROR: OCIStmtExecute/Describe)' (err#0)
    <- prepare('Select rows_added,start_datetime,stop_datetime FROM kc
+rei_batch_job_history WHERE master_job_no = 123 AND master_job_sub_no
+ = 0')= undef at kc_tlog_monitor.pl line 1151
       ERROR: '3113' 'Error while trying to retrieve text for error OR
+A-03113 (DBD ERROR: OCIStmtExecute/Describe)' (err#0)
    <- errstr= 'Error while trying to retrieve text for error ORA-0311
+3 (DBD ERROR: OCIStmtExecute/Describe)' at kc_tlog_monitor.pl line 11
+51
       ERROR: '3113' 'Error while trying to retrieve text for error OR
+A-03113 (DBD ERROR: OCIStmtExecute/Describe)' (err#0)
    <- DESTROY(DBI::st=HASH(84d52b4))= undef
    <- disconnect_all= (not implemented) at DBI.pm line 716
    !! ERROR: '3114' 'Error while trying to retrieve text for error OR
+A-03113 (DBD ERROR: OCIStmtExecute/Describe) [err was 3113 now 3114]
Error while trying to retrieve text for error ORA-03114 (DBD ERROR: OC
+ITransRollback)' (err#1)
!   <- DESTROY(DBI::db=HASH(84e2148))= undef during global destruction
!   <- DESTROY(DBI::dr=HASH(84d5590))= undef during global destruction
[download]

I tried connecting again to the DB but still the same error. What I should do? I'm just been programming in perl for a couple of month, and I'm modifying those programs to run in Linux and Oracle. Win and Oracle using ODBC, works fine, so, it must to be something in my linux computer or DBD, but I am not sure where to look. ANY HELP is highly appreciated!

Eugenia

Comment on DBI - DBD:Oracle problem? Select or Download Code

Replies are listed 'Best First'.
Re: DBI - DBD:Oracle problem? by almut (Canon) on Jun 10, 2008 at 19:59 UTC
Maybe you could try to set InactiveDestroy — in your program #1, that is. In case you're using `system` to run the program, you might need to switch to using `fork` and `exec` to have the necessary degree of control. (Just an idea, i.e. untested...)	[reply] [d/l] [select]
Re^2: DBI - DBD:Oracle problem? by eugemz (Initiate) on Jun 11, 2008 at 13:29 UTC
I love you perl monks! the InactiveDestroy made the trick, and the programs is working perfect now! THANKS!	[reply]
Re: DBI - DBD:Oracle problem? by Corion (Patriarch) on Jun 10, 2008 at 19:52 UTC
It must be something with the machine, the Oracle configuration and/or the network. Your client library seems to be badly configured because it doesn't retrieve the error text for ORA-03113. Google does though, and all it tells me is that ORA-03113 is a generic error. More investigation is needed - likely your DBA and/or sysadmin can help.	[reply]
Re: DBI - DBD:Oracle problem? by psini (Deacon) on Jun 10, 2008 at 20:59 UTC
I apologize in advance for the stupid question, but you provided no code. #1 and #2 are really two different programs? Are you not perhaps forking #1 from #2? Because that is the problem I'd expect if you are forking a program distributing a single DB connection among the children: when the first child that terminates close the connection, the other children will crash. Careful with that hash Eugene.	[reply]