Problem resolved: ora_envhp The first time a connection is made a new OCI 'environment' is created by DBD::Oracle and stored in the driver handle. Subsequent connects reuse (share) that same OCI environment by default.
The ora_envhp attribute can be used to disable the reuse of the OCI environment from a previous connect. If the value is 0 then a new OCI environment is allocated and used for this connection. ________________________________ From: Ransford Hewitt [mailto:[EMAIL PROTECTED] Sent: Sunday, November 02, 2008 1:42 PM To: [email protected] Subject: New DBI user Hello, My program has the potential to access multiple databases. It will first check to ensure that the specific schema handle is not available before creating a new one. In other words I don't want to tear down a connection if the next query is going to same schema. What is happening is that in some cases if the previous connection fails with say invalid userid/passwd, then the next request to create a new database handle fails without giving any clear indication why even though the database credentials are correct. I don't know how to deal with the database handle after such a failure since it is not defined. ... print "DEBUG:get_recordset:before:state - $DBI::state\n"; unless($conn_string eq $prev_conn_string){ $dbh->disconnect if defined($dbh); $prev_conn_string = $conn_string; print "DEBUG:get_recordset:before:***$username***,***$passwd***,***$data_sourc e***\n" ; $dbh = DBI->connect( "dbi:Oracle:$data_source", $username, $passwd, \%attr ); print "DEBUG:get_recordset:after:$username,$passwd,$data_source\n"; unless(defined($dbh)){ #undef $dbh; print "DEBUG:get_recordset:ERROR:$username,$passwd,$data_source\n"; $db_error = 1; print "DEBUG:get_recordset:state - $DBI::state\n"; push @rs,$DBI::errstr; } print "DEBUG:get_recordset:error - $DBI::errstr\n"; } ... [Trace level 3] DBI 1.607-ithread default trace level set to 0x0/2 (pid 4571) at db_copy_ban.pl line 698 via db_copy_ban.pl line 2537 -> $DBI::state (") FETCH from lasth=none <- $DBI::state= undef -> DBI->connect(dbi:Oracle:USG1RO, rcst01_01, ****, HASH(0x400ea8f0)) -> DBI->install_driver(Oracle) for hpux perl=5.008003 pid=4571 ruid=6157 euid=6157 install_driver: DBD::Oracle version 1.22 loaded from /vol09/rgs/test/webadm/usr/local/lib/site_perl/5.8.3/PA-RISC1.1-thread-m ulti/DBD/Oracle.pm -> STORE in DBD::_::common for DBD::Oracle::dr (DBI::dr=HASH(0x403c8b40)~0x403c8ba0 'ShowErrorStatement' 1) thr#40017db0 <- STORE= 1 at Oracle.pm line 68 <- install_driver= DBI::dr=HASH(0x403c8b40) -> connect for DBD::Oracle::dr (DBI::dr=HASH(0x403c8b40)~0x403c8ba0 'USG1RO' 'rcst01_01' **** HASH(0x4012c35c)) thr#40017db0 <- connect= DBI::db=HASH(0x404258b8) at DBI.pm line 638 -> STORE for DBD::Oracle::db (DBI::db=HASH(0x40424890)~INNER 'RaiseError' 0) thr#40017db0 <- STORE= 1 at DBI.pm line 690 -> STORE for DBD::Oracle::db (DBI::db=HASH(0x40424890)~INNER 'PrintError' 0) thr#40017db0 <- STORE= 1 at DBI.pm line 690 -> STORE for DBD::Oracle::db (DBI::db=HASH(0x40424890)~INNER 'AutoCommit' 1) thr#40017db0 <- STORE= 1 at DBI.pm line 690 -> STORE for DBD::Oracle::db (DBI::db=HASH(0x40424890)~INNER 'ShowErrorStatement' 1) thr#40017db0 <- STORE= 1 at DBI.pm line 693 -> FETCH for DBD::Oracle::db (DBI::db=HASH(0x40424890)~INNER 'ShowErrorStatement') thr#40017db0 <- FETCH= 1 at DBI.pm line 693 -> STORE for DBD::Oracle::db (DBI::db=HASH(0x40424890)~INNER 'Username' 'rcst01_01') thr#40017db0 <- STORE= 1 at DBI.pm line 693 <> FETCH= 'rcst01_01' ('Username' from cache) at DBI.pm line 693 -> connected in DBD::_::db for DBD::Oracle::db (DBI::db=HASH(0x404258b8)~0x40424890 'dbi:Oracle:USG1RO' 'rcst01_01' 'rcst01_01' HASH(0x400ea8f0)) thr#40017db0 <- connected= undef at DBI.pm line 699 <- connect= DBI::db=HASH(0x404258b8) -> STORE for DBD::Oracle::db (DBI::db=HASH(0x40424890)~INNER 'dbi_connect_closure' CODE(0x4040e07c)) thr#40017db0 <- STORE= 1 at DBI.pm line 708 -> $DBI::errstr (&) FETCH from lasth=HASH <- $DBI::errstr= undef -> $DBI::state (") FETCH from lasth=HASH <- $DBI::state= '' -> prepare for DBD::Oracle::db (DBI::db=HASH(0x404258b8)~0x40424890 'SELECT sysdate FROM dual ') thr#40017db0 fbh 1: 'SYSDATE' NULLable, otype 12-> 5, dbsize 7/76, p75.s0 <- prepare= DBI::st=HASH(0x40425a68) at db_copy_ban.pl line 730 -> execute for DBD::Oracle::st (DBI::st=HASH(0x40425a68)~0x404259cc) thr#40017db0 dbd_st_execute SELECT (out0, lob0)... Statement Execute Mode is 0 (DEFAULT) dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0) <- execute= '0E0' at db_copy_ban.pl line 730 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0x404259cc)~INNER 'NUM_OF_FIELDS') thr#40017db0 <- FETCH= 1 at db_copy_ban.pl line 360 -> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x40425a68)~0x404259cc) thr#40017db0 <- fetchrow_array= ( '02-NOV-08' ) [1 items] row1 at db_copy_ban.pl line 363 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0x404259cc)~INNER 'NAME_lc') thr#40017db0 <- FETCH= [ 'sysdate' ] at db_copy_ban.pl line 369 -> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x40425a68)~0x404259cc) thr#40017db0 <- fetchrow_array= ( ) [0 items] row1 at db_copy_ban.pl line 378 -> err in DBD::_::common for DBD::Oracle::st (DBI::st=HASH(0x40425a68)~0x404259cc) thr#40017db0 <- err= undef at db_copy_ban.pl line 381 -> $DBI::state (") FETCH from lasth=HASH <- $DBI::state= '' -> disconnect for DBD::Oracle::db (DBI::db=HASH(0x404258b8)~0x40424890) thr#40017db0 <- disconnect= 1 at db_copy_ban.pl line 701 -> DBI->connect(dbi:Oracle:USG1RO, rcst01_20, ****, HASH(0x4042593c)) -> connect for DBD::Oracle::dr (DBI::dr=HASH(0x403c8b40)~0x403c8ba0 'USG1RO' 'rcst01_20' **** HASH(0x404258d0)) thr#40017db0 DESTROY for DBI::db=HASH(0x404c58f0) ignored - handle not initialised !! ERROR: '1017' 'ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)' (err#0) <- connect= undef at DBI.pm line 638 -> $DBI::errstr (&) FETCH from lasth=HASH <- $DBI::errstr= 'ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)' DBI connect('USG1RO','rcst01_20',...) failed: ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin) -> $DBI::state (") FETCH from lasth=HASH <- $DBI::state= 'S1000' -> $DBI::errstr (&) FETCH from lasth=HASH <- $DBI::errstr= 'ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)' -> $DBI::errstr (&) FETCH from lasth=HASH <- $DBI::errstr= 'ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)' -> $DBI::state (") FETCH from lasth=HASH <- $DBI::state= 'S1000' -> $DBI::state (") FETCH from lasth=HASH <- $DBI::state= 'S1000' -> DBI->connect(dbi:Oracle:USG2RO, rcst02_20, ****, HASH(0x40425be8)) !! ERROR: '1017' CLEARED by call to connect method -> connect for DBD::Oracle::dr (DBI::dr=HASH(0x403c8b88)~0x403c8bd0 'USG2RO' 'rcst02_20' **** HASH(0x402d 6620)) thr#40017db0 New 'DBI::db' (for DBD::Oracle::db, parent=DBI::dr=HASH(0x403c8bd0), id=undef) dbih_setup_handle(DBI::db=HASH(0x40177810)=>DBI::db=HASH(0x404c5910), DBD::Oracle::db, 404c6344, Null!) dbih_make_com(DBI::dr=HASH(0x403c8bd0), 403c9818, DBD::Oracle::db, 148, 404c5964) thr#40017db0 dbih_setup_attrib(DBI::db=HASH(0x404c5910), Err, DBI::dr=HASH(0x403c8bd0)) SCALAR(0x404c6314) (already de fined) dbih_setup_attrib(DBI::db=HASH(0x404c5910), State, DBI::dr=HASH(0x403c8bd0)) SCALAR(0x404c5c88) (already defined) dbih_setup_attrib(DBI::db=HASH(0x404c5910), Errstr, DBI::dr=HASH(0x403c8bd0)) SCALAR(0x404c5c94) (already defined) dbih_setup_attrib(DBI::db=HASH(0x404c5910), TraceLevel, DBI::dr=HASH(0x403c8bd0)) 0 (already defined) dbih_setup_attrib(DBI::db=HASH(0x404c5910), FetchHashKeyName, DBI::dr=HASH(0x403c8bd0)) 'NAME' (already d efined) dbih_setup_attrib(DBI::db=HASH(0x404c5910), HandleSetErr, DBI::dr=HASH(0x403c8bd0)) undef (not defined) dbih_setup_attrib(DBI::db=HASH(0x404c5910), HandleError, DBI::dr=HASH(0x403c8bd0)) undef (not defined) dbih_setup_attrib(DBI::db=HASH(0x404c5910), ReadOnly, DBI::dr=HASH(0x403c8bd0)) undef (not defined) dbih_setup_attrib(DBI::db=HASH(0x404c5910), Profile, DBI::dr=HASH(0x403c8bd0)) undef (not defined) Environment variables: ... OCIHandleAlloc(4049e660,4049f948,OCI_HTYPE_ERROR,0,0)=INVALID_HANDLE <> DESTROY(DBI::db=HASH(0x40177810)) ignored for outer handle (inner DBI::db=HASH(0x404c5910) has ref cnt 1) -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x404c5910)~INNER) thr#40017db0 DESTROY for DBI::db=HASH(0x404c5910) ignored - handle not initialised <- DESTROY= undef DESTROY (dbih_clearcom) (dbh 0x404c5910, com 0x4049f8e0, imp DBD::Oracle::db): FLAGS 0x102011: COMSET Warn PrintWarn ShowErrorStatement PARENT DBI::dr=HASH(0x403c8bd0) KIDS 0 (0 Active) dbih_clearcom 0x404c5910 (com 0x4049f8e0, type 2) done. -- DBI::END ($@: , $!: ) -> disconnect_all for DBD::Oracle::dr (DBI::dr=HASH(0x403c8b88)~0x403c8bd0) thr#40017db0 <- disconnect_all= (not implemented) at /vol09/rgs/test/webadm/usr/local/lib/site_perl/5.8.3/PA-RISC1.1-t hread-multi/DBI.pm line 717 via at ./db_copy_ban.pl line 0 ! <> DESTROY(DBI::st=HASH(0x40425a14)) ignored for outer handle (inner DBI::st=HASH(0x40425948) has ref cnt 1) ! -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x40425948)~INNER) thr#40017db0 dbd_st_destroy (OCIHandleFree skipped during global destruction) ! <- DESTROY= undef during global destruction DESTROY (dbih_clearcom) (sth 0x40425948, com 0x4053c648, imp global destruction): FLAGS 0x182011: COMSET Warn PrintWarn ShowErrorStatement PARENT DBI::db=HASH(0x404248c0) KIDS 0 (0 Active) NUM_OF_FIELDS 1 NUM_OF_PARAMS 0 ! -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x404248c0)~INNER) thr#40017db0 OCIHandleFree(404b99f0,OCI_HTYPE_SESSION)=INVALID_HANDLE Ransford Hewitt Technical Consultant Information Technology ROGERS Communication Inc. Rogers Brampton Campus 8200 Dixie Road Brampton, Ontario L6T 0C1 Tel (647) 747-7461
This e-mail (and attachment(s)) is confidential, proprietary, may be subject to copyright and legal privilege and no related rights are waived. If you are not the intended recipient or its agent, any review, dissemination, distribution or copying of this e-mail or any of its content is strictly prohibited and may be unlawful. All messages may be monitored as permitted by applicable law and regulations and our policies to protect our business. E-mails are not secure and you are deemed to have accepted any risk if you communicate with us by e-mail. If received in error, please notify us immediately and delete the e-mail (and any attachments) from any computer or any storage medium without printing a copy. Ce courriel (ainsi que ses pièces jointes) est confidentiel, exclusif, et peut faire l’objet de droit d’auteur et de privilège juridique; aucun droit connexe n’est exclu. Si vous n’êtes pas le destinataire visé ou son représentant, toute étude, diffusion, transmission ou copie de ce courriel en tout ou en partie, est strictement interdite et peut être illégale. Tous les messages peuvent être surveillés, selon les lois et règlements applicables et les politiques de protection de notre entreprise. Les courriels ne sont pas sécurisés et vous êtes réputés avoir accepté tous les risques qui y sont liés si vous choisissez de communiquer avec nous par ce moyen. Si vous avez reçu ce message par erreur, veuillez nous en aviser immédiatement et supprimer ce courriel (ainsi que toutes ses pièces jointes) de tout ordinateur ou support de données sans en imprimer une copie.
