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:[email protected]] 
Sent: Wednesday, October 05, 2011 1:17 PM
To: Kong, Alan
Cc: [email protected]
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...