Re: DBD::ODBC fails, SQL*Plus works
A side note: DBD::ODBC installs on Cygwin now (it didn't when I tried it a while ago), which has greatly expanded the usefulness of some of my Perl scripts. Thank you, thank you, thank you! -- Reinier Post TU Eindhoven
Re: DBD::ODBC fails, SQL*Plus works
On 05/10/11 17:09, Scott Stansbury wrote:
Folks,
I could use a tip or lead with the following issue:
I have a Windows Server 2008 R2 box with Oracle Instant Client 11.2, with the
SQL*Plus and ODBC modules installed. No other Oracle software is installed.
I am connecting to an Oracle 10g instance. The following SQL*Plus works:
sqlplus
user/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))).
The perl script that contains the following does not:
use DBI;
# Oracle / Remedy info
my $oracle_sid = "PROD77";
my $oracle_host = "10.128 192.15.200:1535";
my $oracle_userid = "user";
my $oracle_passwd = "passwd";
my $dsn = "Driver={Oracle in
instantclient_11_2};server=$oracle_host;sid=$oracle_sid";
my $dbh = DBI->connect("dbi:ODBC:$dsn", "$oracle_userid", "$oracle_passwd");
It returns (after a few seconds) with an ORA-12154 error: TNS:could not resolve
the connect identifier specified ( SQL-08004).
Thanks for any help...
Scott...
Have you created an ODBC DSN for the connection?
I use the following settings (on Linux) but most of these you should be able to
set in the create data source part of the odbc administrator if you select the
oracle driver:
[OracleODBC-11g]
Application Attributes=T
Attributes=W
BatchAutocommitMode=IfAllSuccessful
BindAsFLOAT=F
CloseCursor=F
DisableDPM=F
DisableMTS=T
Driver=Oracle 11g ODBC driver
DSN=OracleODBC-11g
EXECSchemaOpt=
EXECSyntax=T
Failover=T
FailoverDelay=10
FailoverRetryCount=10
FetchBufferSize=64000
ForceWCHAR=F
Lobs=T
Longs=T
MetadataIdDefault=F
QueryTimeout=T
ResultSets=T
ServerName=//xxx.easysoft.local/name_of_sid
SQLGetData extensions=F
Translation DLL=
Translation Option=0
DisableRULEHint=T
UserID=bet
StatementCache=F
CacheBufferSize=20
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
RE: :ODBC fails, SQL*Plus works
Scott, Sometimes, it is tricky for the Oracle tnsnames.ora configuration entry, the connection string may contain unknown characters. This is happening mostly because of the cut and paste from different editors into the tnsnames.ora file. You may try to connect using sqlplus again by using(for example) $> sqlplus user/pass@myoracledatabase myoracledatabase is an entry in tnsnames.ora looks like: myoracledatabase = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.15.200)(PORT=1535)))( CONNECT_DATA=(SID=PROD77))) It is different than typing the whole connection description in the command prompt as it will bypass tnsnames.ora entry. ODBC is looking for the tnsnames.ora entry for connection description, so using the above connection method, I bet it will fail also. If this is the case, that means your tnsnames.ora entry is somehow corrupted by the unknown characters. What I am usually do is to type the tnsnames.ora entry manually, it should fix your problem. Hope this is the case. Alan From: Scott Stansbury [mailto:sstansb...@aol.com] Sent: Wednesday, October 05, 2011 1:17 PM To: Kong, Alan Cc: dbi-users@perl.org Subject: Re: :ODBC fails, SQL*Plus works On Oct 5, 2011, at 1:00 PM, Kong, Alan wrote: Since you are using DBI::ODBC, you should create an ODBC data source pointing to your Oracle database first, and then calling the connect function by passing the data source name you just created for ODBC data source. That's our standard config, but in this case that is failing also. Configuring the DSN via the ODBC Data Source Administrator control panel and hitting "Test Connection" yields the same error: Unable to connect SQLState=08004 [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified. I added the connection parameters and credentials to the script to see if it was windows talking to the Oracle InstantClient ODBC layer that was causing the problem... Scott...
RE: :ODBC fails, SQL*Plus works
Since you are using DBI::ODBC, you should create an ODBC data source
pointing to your Oracle database first, and then calling the connect
function by passing the data source name you just created for ODBC data
source.
-Original Message-
From: Scott Stansbury [mailto:sstansb...@aol.com]
Sent: Wednesday, October 05, 2011 12:22 PM
To: Nelson, Erick [HDS]
Cc: dbi-users@perl.org
Subject: Re: :ODBC fails, SQL*Plus works
I'm sorry - that was just fat fingers on the cut/paste...
In actuality it is $oracle_host = "192.168.15.200:1535"
On Oct 5, 2011, at 12:13 PM, Nelson, Erick [HDS] wrote:
> Your $oracle_host Perl var looks like it has more that just the host
ip
> address and port in it. What is the 10.128 ?
>
> -Original Message-
> From: Scott Stansbury [mailto:sstansb...@aol.com]
> Sent: Wednesday, October 05, 2011 9:09 AM
> To: dbi-users@perl.org
> Subject: DBD::ODBC fails, SQL*Plus works
>
> Folks,
>
> I could use a tip or lead with the following issue:
>
> I have a Windows Server 2008 R2 box with Oracle Instant Client 11.2,
> with the SQL*Plus and ODBC modules installed. No other Oracle software
> is installed.
>
> I am connecting to an Oracle 10g instance. The following SQL*Plus
works:
>
> sqlplus
>
user/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.
> 168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))).
>
> The perl script that contains the following does not:
>
> use DBI;
>
> # Oracle / Remedy info
>
> my $oracle_sid = "PROD77";
> my $oracle_host = "192.168.15.200:1535";
>
> my $oracle_userid = "user";
> my $oracle_passwd = "passwd";
>
>
> my $dsn = "Driver={Oracle in
> instantclient_11_2};server=$oracle_host;sid=$oracle_sid";
>
> my $dbh = DBI->connect("dbi:ODBC:$dsn", "$oracle_userid",
> "$oracle_passwd");
>
> It returns (after a few seconds) with an ORA-12154 error: TNS:could
not
> resolve the connect identifier specified ( SQL-08004).
>
> Thanks for any help...
>
> Scott...
>
>
>
>
Scott...
RE: :ODBC fails, SQL*Plus works
Well then I'm not sure, except to say why ODBC?
I'm not sure you can equate sqlplus and odbc. Sqlplus uses sqlnet to
connect while odbc uses ... not sure.
Do you not have an oracle client installed on your box?
Can't you put your sqlplus connection string into the oracle
tnsnames.ora file so you could connect like ?
my $dbh = DBI->connect("dbi:Oracle:$tnsname", $user, $pass);
I also highly recommend that you use the 4th argument of the connect and
turn on RaiseError, like this
my $dbh = DBI->connect("dbi:Oracle:$tnsname", $user, $pass, {RaiseError
=> 1});
... so you can 'catch' errors with an eval instead of having to test for
it manually. This will make your code much cleaner.
Another option might be to run on trace levels.
This spits out a whole lotta stuff that may or may not be useful in
deducing your problem.
I'm pretty sure the levels are 1 - 5 where 5 is the most annoying.
DBI->trace($trace_level)
DBI->trace($trace_level, $trace_filename)
-Original Message-
From: Scott Stansbury [mailto:sstansb...@aol.com]
Sent: Wednesday, October 05, 2011 9:22 AM
To: Nelson, Erick [HDS]
Cc: dbi-users@perl.org
Subject: Re: :ODBC fails, SQL*Plus works
I'm sorry - that was just fat fingers on the cut/paste...
In actuality it is $oracle_host = "192.168.15.200:1535"
On Oct 5, 2011, at 12:13 PM, Nelson, Erick [HDS] wrote:
> Your $oracle_host Perl var looks like it has more that just the host
ip
> address and port in it. What is the 10.128 ?
>
> -Original Message-
> From: Scott Stansbury [mailto:sstansb...@aol.com]
> Sent: Wednesday, October 05, 2011 9:09 AM
> To: dbi-users@perl.org
> Subject: DBD::ODBC fails, SQL*Plus works
>
> Folks,
>
> I could use a tip or lead with the following issue:
>
> I have a Windows Server 2008 R2 box with Oracle Instant Client 11.2,
> with the SQL*Plus and ODBC modules installed. No other Oracle software
> is installed.
>
> I am connecting to an Oracle 10g instance. The following SQL*Plus
works:
>
> sqlplus
>
user/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.
> 168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))).
>
> The perl script that contains the following does not:
>
> use DBI;
>
> # Oracle / Remedy info
>
> my $oracle_sid = "PROD77";
> my $oracle_host = "192.168.15.200:1535";
>
> my $oracle_userid = "user";
> my $oracle_passwd = "passwd";
>
>
> my $dsn = "Driver={Oracle in
> instantclient_11_2};server=$oracle_host;sid=$oracle_sid";
>
> my $dbh = DBI->connect("dbi:ODBC:$dsn", "$oracle_userid",
> "$oracle_passwd");
>
> It returns (after a few seconds) with an ORA-12154 error: TNS:could
not
> resolve the connect identifier specified ( SQL-08004).
>
> Thanks for any help...
>
> Scott...
>
>
>
>
Scott...
RE: DBD::ODBC fails, SQL*Plus works
Why even use DBD::ODBC? Why not use DBD::Oracle? Cheers John > Subject: Re: DBD::ODBC fails, SQL*Plus works > From: john...@pharmacy.arizona.edu > Date: Wed, 5 Oct 2011 10:53:48 -0700 > CC: dbi-users@perl.org > > > On Oct 5, 2011, at 9:09 AM, Scott Stansbury wrote: > > > > > It returns (after a few seconds) with an ORA-12154 error: TNS:could not > > resolve the connect identifier specified ( SQL-08004). > > > Basic questions: the script is running in an environment where the env > variables $ORACLE_HOME and $TNS_ADMIN are available? Your tnsnames.ora file > is present and correct? > > (if not, the those vars and put this: > > PROD77 = > (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))) > > in a text file, save it as tnsnames.ora in your $ORACLE_HOME directory and > see if it works now) > > -- > Bruce Johnson > University of Arizona > College of Pharmacy > Information Technology Group > > Institutions do not have opinions, merely customs > >
Re: :ODBC fails, SQL*Plus works
Am I the only one amused by the fact that in a thread about Oracle we have a
thread with writers "Scott" and "Tiger"?
On Wed, Oct 5, 2011 at 9:41 AM, tiger peng wrote:
> You passed the full description to SQL*Plus. Try to assign it to your
> $dsnorCheck where is the tnsnames.ora (and sqlnet.ora)? what is the valuess
> for env varialbles ORACLE_HOME and TNS_ADMIN
>
>
> Why use ODBC driver instead of Oracle?
>
>
>
>
> From: Scott Stansbury
> To: "Nelson, Erick [HDS]"
> Cc: dbi-users@perl.org
> Sent: Wednesday, October 5, 2011 11:22 AM
> Subject: Re: :ODBC fails, SQL*Plus works
>
> I'm sorry - that was just fat fingers on the cut/paste...
>
> In actuality it is $oracle_host = "192.168.15.200:1535"
>
>
> On Oct 5, 2011, at 12:13 PM, Nelson, Erick [HDS] wrote:
> > Your $oracle_host Perl var looks like it has more that just the host ip
> > address and port in it. What is the 10.128 ?
> >
> > -Original Message-
> > From: Scott Stansbury [mailto:sstansb...@aol.com]
> > Sent: Wednesday, October 05, 2011 9:09 AM
> > To: dbi-users@perl.org
> > Subject: DBD::ODBC fails, SQL*Plus works
> >
> > Folks,
> >
> > I could use a tip or lead with the following issue:
> >
> > I have a Windows Server 2008 R2 box with Oracle Instant Client 11.2,
> > with the SQL*Plus and ODBC modules installed. No other Oracle software
> > is installed.
> >
> > I am connecting to an Oracle 10g instance. The following SQL*Plus works:
> >
> > sqlplus
> > user/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.
> > 168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))).
> >
> > The perl script that contains the following does not:
> >
> > use DBI;
> >
> > # Oracle / Remedy info
> >
> > my $oracle_sid = "PROD77";
> > my $oracle_host = "192.168.15.200:1535";
> >
> > my $oracle_userid = "user";
> > my $oracle_passwd = "passwd";
> >
> >
> > my $dsn = "Driver={Oracle in
> > instantclient_11_2};server=$oracle_host;sid=$oracle_sid";
> >
> > my $dbh = DBI->connect("dbi:ODBC:$dsn", "$oracle_userid",
> > "$oracle_passwd");
> >
> > It returns (after a few seconds) with an ORA-12154 error: TNS:could not
> > resolve the connect identifier specified ( SQL-08004).
> >
> > Thanks for any help...
> >
> > Scott...
> >
> >
> >
> >
>
>
>
> Scott...
>
--
Check out my LEGO blog at http://www.brickpile.com/
View my photos at http://flickr.com/photos/billward/
Follow me at http://twitter.com/williamward
Re: DBD::ODBC fails, SQL*Plus works
On Oct 5, 2011, at 1:53 PM, Bruce Johnson wrote: > Basic questions: the script is running in an environment where the env > variables $ORACLE_HOME and $TNS_ADMIN are available? Your tnsnames.ora file > is present and correct? I did have the env variables configured, and the tnsnames.ora is now correct. Thank you. I do have a question, though. In other installations, the combination of Oracle Instant Client and Microsoft's ODBC Data Source Administrator control panel have been sufficient to gain access to an Oracle db, with no need for a tnsnames.ora file. Indeed, Oracle touts that a a benefit of the Instant Client. In the ODBC Data Source Administrator, what they call the "TNS Service Name" is of the format //hostname:port/sid. Could it be that setting up the data source via the control panel only uses the EZCONNECT format, and if the db instance that I was querying didn't have EZCONNECT enabled via it's sqlnet.ora file ( NAMES.DIRECTORY_PATH=(ezconnect, tnsnames) ), I would see the issues that I did? It's possible that I've never run into the issue before as that (EZCONNECT enabled) is the default? Or does this not make sense at all? ;) Once again, thanks for taking the time to help... Scott...
Re: :ODBC fails, SQL*Plus works
On Oct 5, 2011, at 1:47 PM, Kong, Alan wrote: > > It is different than typing the whole connection description in the command > prompt as it will bypass tnsnames.ora entry. ODBC is looking for the > tnsnames.ora entry for connection description, so using the above connection > method, I bet it will fail also. If this is the case, that means your > tnsnames.ora entry is somehow corrupted by the unknown characters. What I am > usually do is to type the tnsnames.ora entry manually, it should fix your > problem. > > Hope this is the case. It appears that was a contributing factor. Thanks for your help! Scott...
Re: DBD::ODBC fails, SQL*Plus works
On Oct 5, 2011, at 9:09 AM, Scott Stansbury wrote: > > It returns (after a few seconds) with an ORA-12154 error: TNS:could not > resolve the connect identifier specified ( SQL-08004). Basic questions: the script is running in an environment where the env variables $ORACLE_HOME and $TNS_ADMIN are available? Your tnsnames.ora file is present and correct? (if not, the those vars and put this: PROD77 = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))) in a text file, save it as tnsnames.ora in your $ORACLE_HOME directory and see if it works now) -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: :ODBC fails, SQL*Plus works
On Oct 5, 2011, at 1:00 PM, Kong, Alan wrote: > Since you are using DBI::ODBC, you should create an ODBC data source > pointing to your Oracle database first, and then calling the connect > function by passing the data source name you just created for ODBC data > source. That's our standard config, but in this case that is failing also. Configuring the DSN via the ODBC Data Source Administrator control panel and hitting "Test Connection" yields the same error: Unable to connect SQLState=08004 [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified. I added the connection parameters and credentials to the script to see if it was windows talking to the Oracle InstantClient ODBC layer that was causing the problem... Scott...
Re: :ODBC fails, SQL*Plus works
You passed the full description to SQL*Plus. Try to assign it to your
$dsnorCheck where is the tnsnames.ora (and sqlnet.ora)? what is the valuess
for env varialbles ORACLE_HOME and TNS_ADMIN
Why use ODBC driver instead of Oracle?
From: Scott Stansbury
To: "Nelson, Erick [HDS]"
Cc: dbi-users@perl.org
Sent: Wednesday, October 5, 2011 11:22 AM
Subject: Re: :ODBC fails, SQL*Plus works
I'm sorry - that was just fat fingers on the cut/paste...
In actuality it is $oracle_host = "192.168.15.200:1535"
On Oct 5, 2011, at 12:13 PM, Nelson, Erick [HDS] wrote:
> Your $oracle_host Perl var looks like it has more that just the host ip
> address and port in it. What is the 10.128 ?
>
> -Original Message-
> From: Scott Stansbury [mailto:sstansb...@aol.com]
> Sent: Wednesday, October 05, 2011 9:09 AM
> To: dbi-users@perl.org
> Subject: DBD::ODBC fails, SQL*Plus works
>
> Folks,
>
> I could use a tip or lead with the following issue:
>
> I have a Windows Server 2008 R2 box with Oracle Instant Client 11.2,
> with the SQL*Plus and ODBC modules installed. No other Oracle software
> is installed.
>
> I am connecting to an Oracle 10g instance. The following SQL*Plus works:
>
> sqlplus
> user/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.
> 168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))).
>
> The perl script that contains the following does not:
>
> use DBI;
>
> # Oracle / Remedy info
>
> my $oracle_sid = "PROD77";
> my $oracle_host = "192.168.15.200:1535";
>
> my $oracle_userid = "user";
> my $oracle_passwd = "passwd";
>
>
> my $dsn = "Driver={Oracle in
> instantclient_11_2};server=$oracle_host;sid=$oracle_sid";
>
> my $dbh = DBI->connect("dbi:ODBC:$dsn", "$oracle_userid",
> "$oracle_passwd");
>
> It returns (after a few seconds) with an ORA-12154 error: TNS:could not
> resolve the connect identifier specified ( SQL-08004).
>
> Thanks for any help...
>
> Scott...
>
>
>
>
Scott...
Re: :ODBC fails, SQL*Plus works
I'm sorry - that was just fat fingers on the cut/paste...
In actuality it is $oracle_host = "192.168.15.200:1535"
On Oct 5, 2011, at 12:13 PM, Nelson, Erick [HDS] wrote:
> Your $oracle_host Perl var looks like it has more that just the host ip
> address and port in it. What is the 10.128 ?
>
> -Original Message-
> From: Scott Stansbury [mailto:sstansb...@aol.com]
> Sent: Wednesday, October 05, 2011 9:09 AM
> To: dbi-users@perl.org
> Subject: DBD::ODBC fails, SQL*Plus works
>
> Folks,
>
> I could use a tip or lead with the following issue:
>
> I have a Windows Server 2008 R2 box with Oracle Instant Client 11.2,
> with the SQL*Plus and ODBC modules installed. No other Oracle software
> is installed.
>
> I am connecting to an Oracle 10g instance. The following SQL*Plus works:
>
> sqlplus
> user/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.
> 168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))).
>
> The perl script that contains the following does not:
>
> use DBI;
>
> # Oracle / Remedy info
>
> my $oracle_sid = "PROD77";
> my $oracle_host = "192.168.15.200:1535";
>
> my $oracle_userid = "user";
> my $oracle_passwd = "passwd";
>
>
> my $dsn = "Driver={Oracle in
> instantclient_11_2};server=$oracle_host;sid=$oracle_sid";
>
> my $dbh = DBI->connect("dbi:ODBC:$dsn", "$oracle_userid",
> "$oracle_passwd");
>
> It returns (after a few seconds) with an ORA-12154 error: TNS:could not
> resolve the connect identifier specified ( SQL-08004).
>
> Thanks for any help...
>
> Scott...
>
>
>
>
Scott...
DBD::ODBC fails, SQL*Plus works
Folks,
I could use a tip or lead with the following issue:
I have a Windows Server 2008 R2 box with Oracle Instant Client 11.2, with the
SQL*Plus and ODBC modules installed. No other Oracle software is installed.
I am connecting to an Oracle 10g instance. The following SQL*Plus works:
sqlplus
user/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))).
The perl script that contains the following does not:
use DBI;
# Oracle / Remedy info
my $oracle_sid = "PROD77";
my $oracle_host = "10.128 192.15.200:1535";
my $oracle_userid = "user";
my $oracle_passwd = "passwd";
my $dsn = "Driver={Oracle in
instantclient_11_2};server=$oracle_host;sid=$oracle_sid";
my $dbh = DBI->connect("dbi:ODBC:$dsn", "$oracle_userid", "$oracle_passwd");
It returns (after a few seconds) with an ORA-12154 error: TNS:could not resolve
the connect identifier specified ( SQL-08004).
Thanks for any help...
Scott...
