Weird TNS error with DBI::Oracle 1.21 & oracle 11G

Perl 5.8, DBI 1.607, DBI::Oracle 1.21, Oracle 11g (11.1.0)

Any script I try to run results in the error:

Database connection not made: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor (DBD ERROR: OCIServerAttach) at ./testdbi.pl line 9.

This script works correctly on our old server running 8.1.6.

tnsping works, so I'm at a loss:

oracle@tonic2:/opt/lampp/logs> tnsping phmweb.pharmacy.arizona.edu

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 29- JUL-2008 11:34:53

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
/home/oracle/product/11.1.0/db_1//network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = tonic2.pharmacy.arizona.edu)(PORT = 1521))) (CONNECT_DATA = (SID = phmweb.pharmacy.arizona.edu)))
OK (0 msec)

The Oracle environment variables are set properly; sqlplus works just fine when invoked using the 'user'@'tnsname_of_server' login, another indication that TNSNames is working.

Here is the code I'm using:

#!/usr/bin/perl
use strict;
use DBI;
#$ENV{'ORACLE_HOME'}="/home/oracle/product/11.1.0/db_1";
#$ENV{'TNS_ADMIN'}="/home/oracle/product/11.1.0/db_1/network/admin";
sub PrintHeader {
return "Content-type: text/html\n\n";
}
my $dbh = DBI->connect( 'dbi:Oracle:phmweb.pharmacy.arizona.edu',
'people',
'password',
) || die "Database connection not made: $DBI::errstr";
my $sql = "select sysdate from dual";
my $ret = $dbh->prepare($sql);
$ret->execute();
print &PrintHeader;
#DBI::dump_results($ret);
my ($thedate);
$ret->bind_columns(\$thedate);
while(($thedate) = $ret->fetchrow_array) {print "Today's date is $thedate \n";}
$dbh->disconnect();
exit;

The ENV stuff was attempts to see if it was some environmental issue, this made no difference.

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs


.

Relevant Pages

  • Re: Database/Email Script
    ... PHP programmer during the interview. ... Still they have given me a script ... //With the database connection open, I start to insert the data from ... //This sets the query to a variable for easy calling ...
    (comp.lang.php)
  • RE: dealing with lengthy (time) query sets
    ... I would put the 'x' records into a QUEUE table. ... My last resort which i know works is to make another script that makes ... its own database connection does its work and dies. ... sender of the delivery error by replying to this message, or notify us by ...
    (perl.dbi.users)
  • Re: Database/Email Script
    ... At the beginning of the script I set each variable to a local ... //This sets the query to a variable for easy calling ... //With the database connection open, ... Numeric values do not need to be enclosed in single quotes, ...
    (comp.lang.php)
  • Re: Database/Email Script
    ... At the beginning of the script I set each variable to a local ... //This sets the query to a variable for easy calling ... //With the database connection open, ... Numeric values do not need to be enclosed in single quotes, ...
    (comp.lang.php)