Igor, Thanks for the pointer, but the $dbh is the same $dbh that is used in the pervious 5 task within the script that work. The more important problem is that when I move the script over to the Oracle 8.1.7 box (that is, the perl has been built with the 8.1.7 libraries) the scripts run! There was no code modification to the script it was simple moved from one machine to another.
Cheers -----Original Message----- From: Igor Korolev [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 11:48 AM To: Warden, Ronald MCF:EX; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: crippling problem with signals and Oracle 9i client libraries In line 317 you obviously have undefined $dbh which means that you either failed to connect or lost connection. -----Original Message----- From: Warden, Ronald MCF:EX [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 1:33 PM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: crippling problem with signals and Oracle 9i client libraries Hello, I am experiencing problems with Oracle 9.2.0 and perl/DBI! I see print statements fail with memory leakage, that is, I see errors like: Modification of a read-only value attempted at unload_table.pl line 245. where line 245 is a print statement to a log file. I also see errors where DBI reports that the script is not connect to Oracle, even though the script has completed 5 tasks on the Oracle database. The error message is: Error on prepare ORA-03114: not connected to ORACLE (DBD ERROR: OCIStmtExecute/Describe) at cr_stage_tables.pl line 317, <STDIN> line 1. Can't call method "execute" without a package or object reference at cr_stage_tables.pl line 328, <STDIN> line 1. While line 317 looks like: $sth = $dbh->prepare($column_data)|| warn "Error on prepare $DBI::errstr"; The script moved to an 8.1.7 Oracle instance works JUST FINE!!!! I don't have any luxury of going back to 8.1.7 since there are features that failed until 8.1.7 that I need to work for a production system. I am kluging things at the moment by running scripts on my dev box with is 8.1.7 and talking to the 9.2.0 production Oracle database. Not a long term solution. If some one wants a trace or some help debugging this let me know! Cheers Ron -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 8:30 AM To: [EMAIL PROTECTED] Subject: FW: crippling problem with signals and Oracle 9i client libriarie s Hi All, I thought I would share this message with you, in case you a) did not know of this problem, or b) you know of a "cure" for this problem. I do not think this a PERL or a DBI issue per se, but it affects the users of DBI. Comments/Information welcome... Lincoln Sent to our internal DBA support group: -----Original Message----- From: me Subject: crippling problem with signals and Oracle 9i client libriaries Hi all, I have found what I believe to be a problem with the Oracle 9.2.0 (9i) client libraries and signal handling which I think is crippling. It will PREVENT us from fully upgrading to Oracle 9i until it can be fixed. We all know that Oracle client hangs for a VERY long time (something like 10 minutes), if sqlnet can not reach the box it is told a database is on (i.e. the box is down). We have worked around this "bug" with the use of SIGALRM as illustrated in the test program which follows: #!/usr/bin/env perl use strict; use warnings; use DBI; use Time::HiRes qw( ualarm ); my ( $timeout ) = @ARGV; $timeout = 3 if not defined $timeout; print "using timeout of $timeout seconds\n" ; eval { local $SIG{ALRM} = sub { die "connect timeout after $timeout seconds" ; }; ualarm($timeout * 1_000_000); my $dbh = DBI->connect( "dbi:Oracle:dbfail" ,"foo" ,"bar" ,{ AutoCommit=>0 ,RaiseError=>1 ,PrintError=>1 } ); }; ualarm(0); if ( $@ ) { die $@; } With the Oracle 8.x libraries this code hangs until the timer expires (default=3 seconds), and then prints: connect timeout after 3 seconds at ./test.connect.dbfail.pl line 16. This is the desired behavior, because it returns control to the program, which can then handle the error. I have run this code against: perl 5.6.1 and Oracle 8.1.7 (sun and HP) (succeeds) perl 5.6.1 and Oracle 8.1.5 (linux) (succeeds) perl 5.8.0 and Oracle 8.1.5 (linux) (succeeds) perl 5.8.0 and Oracle 9.2.0 (sun and HP) (FAILS) With Oracle 9.x libraries this code hangs and never returns. Further, the only signal the process will accept is KILL (9), which it has to accept. This tells me that the 9.x libraries are disabling all or most signals, making it impossible for us to use the Oracle 9i libraries in servers. DBAs: Please research this on OTN, and if necesssary open a bug report with Oracle. THIS IS A BLOCKING ISSUE for completing the 9.2 Oracle upgrade, and being able to remove the 8.1.7 client. If we can not get a resolution of this issue from Oracle, I will have to rebuild the perl 5.8.0 tree with Oracle 8.1.7 libraries (instead of the 9.2.0 libraries it is currently linked with). I hope Oracle already has a patch for this problem. Lincoln PS, although I think it not relevant, this is DBI version 1.37 and DBD::Oracle version 1.14. I believe I would be able to replicate this with earlier versions of DBI and DBD:Oracle.
