Insights into DBI->connect differences

Hello, I'm hoping someone can explain why one of these connect
statements work and the other doesn't.
The script sets environment values as such:
$ENV{ORACLE_HOME} =3D "/usr/oracle/product/8.1.7_64";
$ENV{ORACLE_SID} =3D $db_alias; #$db_alias passed to script via
$ARGV[1]
my $dbh;
if ($method =3D~ /2/) { # $method passed to script via $ARGV[2]
#this works
$dbh =3D DBI->connect('', "scott/tiger", '', 'Oracle') =20
or die "Connect failed: $DBI::errstr\n";
}else{ #this doesn't
$dbh =3D DBI->connect("dbi:Oracle:$db_alias",'scott','tiger')
or die "Connect failed: $DBI::errstr\n";
}
The error I'm getting on the latter case is: DBI->connect(mp3i) failed:
ORA-12154: TNS:could not resolve service name (DBD ERROR:
OCIServerAttach) at ./OraConn3.pl line 49
Connect failed: ORA-12154: TNS:could not resolve service name (DBD
ERROR: OCIServerAttach)

Does this imply that the first case does not use TNS service names? =20
The DBI docs state: "There is no standard for the text following the
driver name. Each driver is free to use whatever syntax it wants. The
only requirement the DBI makes is that all the information is supplied
in a single string. You must consult the documentation for the drivers
you are using for a description of the syntax they require." The
DBD::Oracle doc doesn't go into detail on the variations and what they
imply.
Can anyone provide some insight? =20

The issue I'm really trying to address is that both connection
variations work on an HP/UX 11.00 server, perl version: 5.006001, DBI
(version 1.20), DBD::Oracle (version 1.12); but on an HP/UX 11.11 server
with the same perl and oracle libraries/versions only the first
variation works. I'm trying to determine why and suggest a resolution.
BTW, I'm a lowly programmer (aka: hacker); I'm not the DBA or the server
sysadmin and the DBA doesn't know perl.

Thanks in advance.
Paula =20

Re: Insights into DBI->connect differences

Capacio, Paula J wrote:

>Hello, I'm hoping someone can explain why one of these connect
>statements work and the other doesn't.
>The script sets environment values as such:
>$ENV{ORACLE_HOME} = "/usr/oracle/product/8.1.7_64";
>$ENV{ORACLE_SID} = $db_alias; #$db_alias passed to script via
>$ARGV[1]
>my $dbh;
>
>
Paula, print these two variables out before the entry into the logical
test - just to see what they are. Send this back in a reply. Make this
the case where the "else" clause should execute, as an example.

print "$db_alias =? $ENV{ORACLE_SID}\n";

>if ($method =~ /2/) { # $method passed to script via $ARGV[2]
> #this works
> $dbh = DBI->connect('', "scott/tiger", '', 'Oracle')
> or die "Connect failed: $DBI::errstr\n";
>}else{ #this doesn't
> $dbh = DBI->connect("dbi:Oracle:$db_alias",'scott','tiger')
> or die "Connect failed: $DBI::errstr\n";
>}
>The error I'm getting on the latter case is: DBI->connect(mp3i) failed:
>ORA-12154: TNS:could not resolve service name (DBD ERROR:
>OCIServerAttach) at ./OraConn3.pl line 49
>Connect failed: ORA-12154: TNS:could not resolve service name (DBD
>ERROR: OCIServerAttach)
>
>Does this imply that the first case does not use TNS service names?
>The DBI docs state: "There is no standard for the text following the
>driver name. Each driver is free to use whatever syntax it wants. The
>only requirement the DBI makes is that all the information is supplied
>in a single string. You must consult the documentation for the drivers
>you are using for a description of the syntax they require." The
>DBD::Oracle doc doesn't go into detail on the variations and what they
>imply.
>Can anyone provide some insight?
>
>The issue I'm really trying to address is that both connection
>variations work on an HP/UX 11.00 server, perl version: 5.006001, DBI
>(version 1.20), DBD::Oracle (version 1.12); but on an HP/UX 11.11 server
>with the same perl and oracle libraries/versions only the first
>variation works. I'm trying to determine why and suggest a resolution.
>BTW, I'm a lowly programmer (aka: hacker); I'm not the DBA or the server
>sysadmin and the DBA doesn't know perl.
>
>Thanks in advance.
>Paula
>
>

RE: Insights into DBI->connect differences

The Oracle error docs are your friend:
http://download-west.oracle.com/docs/cd/B10501_01/server.920 /a96525.pdf

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Capacio, Paula J [mailto:PCAPACIO@amfam.com]=20
Sent: Wednesday, February 15, 2006 1:37 PM
To: dbi-users@perl.org
Subject: Insights into DBI->connect differences


Hello, I'm hoping someone can explain why one of these connect
statements work and the other doesn't. The script sets environment
values as such: $ENV{ORACLE_HOME} =3D "/usr/oracle/product/8.1.7_64";
$ENV{ORACLE_SID} =3D $db_alias; #$db_alias passed to script via
$ARGV[1]
my $dbh;
if ($method =3D~ /2/) { # $method passed to script via $ARGV[2]
#this works
$dbh =3D DBI->connect('', "scott/tiger", '', 'Oracle') =20
or die "Connect failed: $DBI::errstr\n";
}else{ #this doesn't
$dbh =3D DBI->connect("dbi:Oracle:$db_alias",'scott','tiger')
or die "Connect failed: $DBI::errstr\n";
}
The error I'm getting on the latter case is: DBI->connect(mp3i) failed:
ORA-12154: TNS:could not resolve service name (DBD ERROR:
OCIServerAttach) at ./OraConn3.pl line 49
Connect failed: ORA-12154: TNS:could not resolve service name (DBD
ERROR: OCIServerAttach)

Does this imply that the first case does not use TNS service names? =20
The DBI docs state: "There is no standard for the text following the
driver name. Each driver is free to use whatever syntax it wants. The
only requirement the DBI makes is that all the information is supplied
in a single string. You must consult the documentation for the drivers
you are using for a description of the syntax they require." The
DBD::Oracle doc doesn't go into detail on the variations and what they
imply. Can anyone provide some insight? =20

The issue I'm really trying to address is that both connection
variations work on an HP/UX 11.00 server, perl version: 5.006001, DBI
(version 1.20), DBD::Oracle (version 1.12); but on an HP/UX 11.11 server
with the same perl and oracle libraries/versions only the first
variation works. I'm trying to determine why and suggest a resolution.
BTW, I'm a lowly programmer (aka: hacker); I'm not the DBA or the server
sysadmin and the DBA doesn't know perl.

Thanks in advance.
Paula =20

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: Insights into DBI->connect differences

Sorry, pushed the send button too fast ...

You also should set your oracle environment using the shell script
oraenv. There are many other env vars that need to be set in order for
the DBD to load and function correctly. Again, see the link I sent
earlier and set for oraenv.

-----Original Message-----
From: Reidy, Ron=20
Sent: Wednesday, February 15, 2006 9:19 PM
To: Capacio, Paula J; dbi-users@perl.org
Subject: RE: Insights into DBI->connect differences


The Oracle error docs are your friend:
http://download-west.oracle.com/docs/cd/B10501_01/server.920 /a96525.pdf

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Capacio, Paula J [mailto:PCAPACIO@amfam.com]=20
Sent: Wednesday, February 15, 2006 1:37 PM
To: dbi-users@perl.org
Subject: Insights into DBI->connect differences


Hello, I'm hoping someone can explain why one of these connect
statements work and the other doesn't. The script sets environment
values as such: $ENV{ORACLE_HOME} =3D "/usr/oracle/product/8.1.7_64";
$ENV{ORACLE_SID} =3D $db_alias; #$db_alias passed to script via
$ARGV[1]
my $dbh;
if ($method =3D~ /2/) { # $method passed to script via $ARGV[2]
#this works
$dbh =3D DBI->connect('', "scott/tiger", '', 'Oracle') =20
or die "Connect failed: $DBI::errstr\n";
}else{ #this doesn't
$dbh =3D DBI->connect("dbi:Oracle:$db_alias",'scott','tiger')
or die "Connect failed: $DBI::errstr\n";
}
The error I'm getting on the latter case is: DBI->connect(mp3i) failed:
ORA-12154: TNS:could not resolve service name (DBD ERROR:
OCIServerAttach) at ./OraConn3.pl line 49
Connect failed: ORA-12154: TNS:could not resolve service name (DBD
ERROR: OCIServerAttach)

Does this imply that the first case does not use TNS service names? =20
The DBI docs state: "There is no standard for the text following the
driver name. Each driver is free to use whatever syntax it wants. The
only requirement the DBI makes is that all the information is supplied
in a single string. You must consult the documentation for the drivers
you are using for a description of the syntax they require." The
DBD::Oracle doc doesn't go into detail on the variations and what they
imply. Can anyone provide some insight? =20

The issue I'm really trying to address is that both connection
variations work on an HP/UX 11.00 server, perl version: 5.006001, DBI
(version 1.20), DBD::Oracle (version 1.12); but on an HP/UX 11.11 server
with the same perl and oracle libraries/versions only the first
variation works. I'm trying to determine why and suggest a resolution.
BTW, I'm a lowly programmer (aka: hacker); I'm not the DBA or the server
sysadmin and the DBA doesn't know perl.

Thanks in advance.
Paula =20

This electronic message transmission is a PRIVATE communication which
contains information which may be confidential or privileged. The
information is intended=20
to be for the use of the individual or entity named above. If you are
not the=20
intended recipient, please be aware that any disclosure, copying,
distribution=20
or use of the contents of this information is prohibited. Please notify
the sender of the delivery error by replying to this message, or notify
us by telephone (877-633-2436, ext. 0), and then delete it from your
system.


This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: Insights into DBI->connect differences

Capacio, Paula J wrote:

>>Hello, I'm hoping someone can explain why one of these connect
>>statements work and the other doesn't. The script sets environment=20
>>values as such: $ENV{ORACLE_HOME} =3D "/usr/oracle/product/8.1.7_64";
>>$ENV{ORACLE_SID} =3D $db_alias; #$db_alias passed to script via
>>$ARGV[1]
>>my $dbh;
>> =20
>
Louis wrote:
>Paula, print these two variables out before the entry into the logical
>test - just to see what they are. Send this back in a reply. Make
this=20
>the case where the "else" clause should execute, as an example.
>print "$db_alias =3D? $ENV{ORACLE_SID}\n";
The code now looks like:
my $dbh;
if ($method =3D~ /2/) { #this works
$dbh =3D DBI->connect('', "scott/tiger", '', 'Oracle')=20
or die "Connect failed: $DBI::errstr\n";
}else{ #this doesn't
printIt("Just prior to connect...");
printIt("$db_alias =3D? $ENV{ORACLE_SID}");
printIt("\$ENV{ORACLE_HOME} to: $ENV{ORACLE_HOME}\n");
$dbh =3D DBI->connect("dbi:Oracle:$db_alias",'scott','tiger')
or die "Connect failed: $DBI::errstr\n";
}
And the new output:
Just prior to connect...
mp3i =3D? mp3i
$ENV{ORACLE_HOME} to: /usr/oracle/product/8.1.7_64
DBI->connect(mp3i) failed: ORA-12154: TNS:could not resolve service name

(DBD ERROR: OCIServerAttach) at ./OraConn3.pl line 52
Connect failed: ORA-12154: TNS:could not resolve service name (DBD
ERROR: OCIServerAttach)

>>Does this imply that the first case does not use TNS service names?=20
>>The DBI docs state: "There is no standard for the text following the=20
>>driver name. Each driver is free to use whatever syntax it wants. The=20
>>only requirement the DBI makes is that all the information is supplied

>>in a single string. You must consult the documentation for the drivers

>>you are using for a description of the syntax they require." The=20
>>DBD::Oracle doc doesn't go into detail on the variations and what they

>>imply. Can anyone provide some insight?
>>
>>The issue I'm really trying to address is that both connection
>>variations work on an HP/UX 11.00 server, perl version: 5.006001, DBI=20
>>(version 1.20), DBD::Oracle (version 1.12); but on an HP/UX 11.11=20
>>server with the same perl and oracle libraries/versions only the first

>>variation works. I'm trying to determine why and suggest a
resolution.=20
>>BTW, I'm a lowly programmer (aka: hacker); I'm not the DBA or the=20
>>server sysadmin and the DBA doesn't know perl.
>
>Thanks in advance.
>Paula =20
> =20
>

RE: Insights into DBI->connect differences

Capacio, Paula J [mailto:PCAPACIO@amfam.com] wrote:

> The code now looks like:
> my $dbh;
> if ($method =~ /2/) { #this works
> $dbh = DBI->connect('', "scott/tiger", '', 'Oracle')
> or die "Connect failed: $DBI::errstr\n";
> }else{ #this doesn't
> printIt("Just prior to connect...");
> printIt("$db_alias =? $ENV{ORACLE_SID}");
> printIt("\$ENV{ORACLE_HOME} to: $ENV{ORACLE_HOME}\n");
> $dbh = DBI->connect("dbi:Oracle:$db_alias",'scott','tiger')
> or die "Connect failed: $DBI::errstr\n";
> }
> And the new output:
> Just prior to connect...
> mp3i =? mp3i
> $ENV{ORACLE_HOME} to: /usr/oracle/product/8.1.7_64
> DBI->connect(mp3i) failed: ORA-12154: TNS:could not resolve service name
>
> (DBD ERROR: OCIServerAttach) at ./OraConn3.pl line 52
> Connect failed: ORA-12154: TNS:could not resolve service name (DBD
> ERROR: OCIServerAttach)

Have you checked the tnsnames.ora file to make sure mp3i is defined?

Ronald

Re: Insights into DBI->connect differences

Is: mp3i
listed in your: tnsnames.ora file?
From what I see, it really looks like an issue with your "mp3i" not
being a known service.

Capacio, Paula J wrote:

>Capacio, Paula J wrote:
>
>
>
>>>Hello, I'm hoping someone can explain why one of these connect
>>>statements work and the other doesn't. The script sets environment
>>>values as such: $ENV{ORACLE_HOME} = "/usr/oracle/product/8.1.7_64";
>>>$ENV{ORACLE_SID} = $db_alias; #$db_alias passed to script via
>>>$ARGV[1]
>>>my $dbh;
>>>
>>>
>>>
>Louis wrote:
>
>
>>Paula, print these two variables out before the entry into the logical
>>test - just to see what they are. Send this back in a reply. Make
>>
>>
>this
>
>
>>the case where the "else" clause should execute, as an example.
>>print "$db_alias =? $ENV{ORACLE_SID}\n";
>>
>>
>The code now looks like:
>my $dbh;
>if ($method =~ /2/) { #this works
> $dbh = DBI->connect('', "scott/tiger", '', 'Oracle')
> or die "Connect failed: $DBI::errstr\n";
>}else{ #this doesn't
> printIt("Just prior to connect...");
> printIt("$db_alias =? $ENV{ORACLE_SID}");
> printIt("\$ENV{ORACLE_HOME} to: $ENV{ORACLE_HOME}\n");
> $dbh = DBI->connect("dbi:Oracle:$db_alias",'scott','tiger')
> or die "Connect failed: $DBI::errstr\n";
>}
>And the new output:
>Just prior to connect...
>mp3i =? mp3i
>$ENV{ORACLE_HOME} to: /usr/oracle/product/8.1.7_64
>DBI->connect(mp3i) failed: ORA-12154: TNS:could not resolve service name
>
> (DBD ERROR: OCIServerAttach) at ./OraConn3.pl line 52
>Connect failed: ORA-12154: TNS:could not resolve service name (DBD
>ERROR: OCIServerAttach)
>
>
>
>>>Does this imply that the first case does not use TNS service names?
>>>The DBI docs state: "There is no standard for the text following the
>>>driver name. Each driver is free to use whatever syntax it wants. The
>>>only requirement the DBI makes is that all the information is supplied
>>>
>>>
>
>
>
>>>in a single string. You must consult the documentation for the drivers
>>>
>>>
>
>
>
>>>you are using for a description of the syntax they require." The
>>>DBD::Oracle doc doesn't go into detail on the variations and what they
>>>
>>>
>
>
>
>>>imply. Can anyone provide some insight?
>>>
>>>The issue I'm really trying to address is that both connection
>>>variations work on an HP/UX 11.00 server, perl version: 5.006001, DBI
>>>(version 1.20), DBD::Oracle (version 1.12); but on an HP/UX 11.11
>>>server with the same perl and oracle libraries/versions only the first
>>>
>>>
>
>
>
>>>variation works. I'm trying to determine why and suggest a
>>>
>>>
>resolution.
>
>
>>>BTW, I'm a lowly programmer (aka: hacker); I'm not the DBA or the
>>>server sysadmin and the DBA doesn't know perl.
>>>
>>>
>>Thanks in advance.
>>Paula
>>
>>
>>
>>

RE: Insights into DBI->connect differences

Capacio, Paula J [mailto:PCAPACIO@amfam.com] wrote:
>> The code now looks like:
>> my $dbh;
>> if ($method =3D~ /2/) { #this works
>> $dbh =3D DBI->connect('', "scott/tiger", '', 'Oracle')
>> or die "Connect failed: $DBI::errstr\n";
>> }else{ #this doesn't
>> printIt("Just prior to connect...");
>> printIt("$db_alias =3D? $ENV{ORACLE_SID}");
>> printIt("\$ENV{ORACLE_HOME} to: $ENV{ORACLE_HOME}\n");
>> $dbh =3D DBI->connect("dbi:Oracle:$db_alias",'scott','tiger')
>> or die "Connect failed: $DBI::errstr\n";
>> }
>> And the new output:
>> Just prior to connect...
>> mp3i =3D? mp3i
>> $ENV{ORACLE_HOME} to: /usr/oracle/product/8.1.7_64
>> DBI->connect(mp3i) failed: ORA-12154: TNS:could not resolve service=20
>> DBI->name
>>=20
>> (DBD ERROR: OCIServerAttach) at ./OraConn3.pl line 52 Connect=20
>> failed: ORA-12154: TNS:could not resolve service name (DBD
>> ERROR: OCIServerAttach)

Ronald J Kimball wrote:
>Have you checked the tnsnames.ora file to make sure mp3i is defined?

This is solved: For whatever reason, our tnsnames.ora file is located
in=20
the /etc directory instead of the /network/admin found via ORACLE_HOME.
Once I added this environment variable it all worked fine:
$ENV{TNS_ADMIN} =3D"/etc"; =20

Thanks all.