Error when connecting to Oracle from ODBC

[srinivas.nettalam]

Hi All,
We are getting an error when we try to connect to Oracle using ODBC connector.

[IBM(DataDirect OEM)][ODBC Oracle Wire Protocol driver][Oracle]TNS-12505: TNS:listener could not resolve SID given in connect descriptor

I talked to DBA and the SID name is correct which we entered in odbc.ini.
I doubt whether '-' in the HostName field is creating problem.
We need to execute Oracle Stored Procedure from datastage and as far as I know we must make Oracle as an ODBC to execute stored procedures.Please let me know if I am wrong as well as suggest on the entry below from .odbc.ini

Code: Select all

[Ora_AITBROKER]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMora25.so
Description=DataDirect Oracle Wire Protocol driver
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName=ait-dbdev.woolworths.co.za
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=
Password=
PortNumber=1521
ProcedureRetResults=0
SID=aitdev
UseCurrentSchema=1

[Madhumitha_Raghunathan]

If It is giving an error from TNS, I think u may want to check if the TNS entry in the tnsnames.ora file in your oracle client's Network/Admin path is correct.
Also you can try tnsping <TNSEntry> to verify if the TNS entry is correct.
We have had lot of issues like these mainly because the TNS entry for that db had some problem.

[srinivas.nettalam]

Hi,
The DB has two instances with two different SID names.We connected to one of the instances.

[stuartjvnorton]

What does your tnsnames.ora look like?

If you've got 2 instances of the 1 database, you may have a RAC setup. In that case, refer to the ServiceName in odbc.ini instead of a SID.

[srinivas.nettalam]

Hi Norton,
We connect to that DB referring to Service Name but the ODBC Wire Protocol connection does not have the field to enter ServiceName in the list.Hence the SID.Please let me know if there is a way to make an ODBC connection using Oracle wire protocol entries for ServiceName

[chulett]

I doubt you enter either the actual SID or ServiceName there, have you tried using the name of the TNS entry there? It would then specify the actual connection information to use.

[srinivas.nettalam]

I doubt you enter either the actual SID or ServiceName there, have you tried using the name of the TNS entry there? It would then specify the actual connection information to use. ...

The connection issue is resolved and we are able to connect to database and import metadata of the stored procedure.But when executing it from job we are getting the error

Code: Select all

Stored_Procedure_0,0: Error: ORA-12154: TNS:could not resolve the connect identifier specified

To execute a stored procedure ,does a connection need to be only ODBC??We are able to import metadata but not execute..Very much confused..Please help

[srinivas.nettalam]

I have entered the database name instead of ODBC DSN and it got connected.