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

Standard troubleshooting applies:
1. Are you running the script as the same user? and with the same
environment? (If you are running it in a webserver probably not).
2. Is this running under apache? If so, are the same environment variables
being set in httpd.conf? Or are the old 8.1.6 variables there maybe?
3. Try running the script with the following code inserted near the
beginning:
foreach my $key (sort keys %ENV){ print "$key=$ENV{$key}\n"} (add in a
<br> if running through a webserver)
Compare the output to `env|sort` when run by the user you are using
tnsping as.
4. Environment variables should be set prior to running the script. Either
by sourcing a setenv.sh or similar script, or by putting them into
httpd.conf.

On Tue, Jul 29, 2008 at 2:51 PM, Bruce Johnson <johnson@xxxxxxxxxxxxxxxxxxxx
wrote:

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





--
They who would give up an essential liberty for temporary security, deserve
neither liberty or security.
Benjamin Franklin

Our lives begin to end the day we become silent about things that matter.
Martin Luther King

The right of the people to be secure in their persons, houses, papers, and
effects, against unreasonable searches and seizures, shall not be violated,
and no warrants shall issue, but upon probable cause, supported by oath or
affirmation, and particularly describing the place to be searched, and the
persons or things to be seized.

Amendment IV to the Constitution of the United States

"I am not going to answer any questions as to my association, my
philosophical or religious beliefs or my political beliefs, or how I voted
in any election, or any of these private affairs. I think these are very
improper questions for any American to be asked, especially under such
compulsion as this."
Pete Seeger before the House Un-American Activities Comittee

Relevant Pages

  • Re: Possible to define a variable for only certain directories?
    ... allows such customizations upon the environment on a per- ... this is useful only if the user or script ... configuration files, environment variables, command line options, and ... commands to the CDE Window Manager from a shell ...
    (comp.unix.shell)
  • RE: local admin account password
    ... environment, yes we may have these, but we still use the same methods. ... With regards to the script, this script is actually run from a secured ... The local passwords are secured both in a secure password ...
    (Focus-Microsoft)
  • Re: Help with system seeming to return too soon...
    ... >>install script. ... > a new process, sets the environment variables in that process, then ... I can on the command line) that I could get around this problem. ...
    (comp.lang.perl.misc)
  • (reposted) Question about CTRL-C handling in scripts
    ... I have a 'wrapper' script that sets up some environment variables and ... then executes another script, which then eventually runs a Fortran ... Then it requires a second <Ctrl-C> to exit this looping and return to ...
    (comp.unix.shell)
  • Re: Script for your enjoyment
    ... Another environment specific aspect. ... I do not maintain any databases ... The script should do that for you. ... isn't anyone checking if a domain group exists before they add it". ...
    (microsoft.public.scripting.wsh)
Quantcast