DSXchange

Posted: **Tue Dec 18, 2012 10:22 am**

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

Posted: **Tue Dec 18, 2012 10:44 am**

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.

Posted: **Fri Dec 21, 2012 3:22 am**

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

Posted: **Fri Dec 21, 2012 5:12 am**

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.

Posted: **Fri Dec 21, 2012 6:48 am**

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

Posted: **Fri Dec 21, 2012 8:30 am**

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.

Posted: **Thu Dec 27, 2012 4:25 am**

chulett wrote: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

Posted: **Thu Dec 27, 2012 5:01 am**

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

DSXchange

Error when connecting to Oracle from ODBC

Error when connecting to Oracle from ODBC