This discussion is archived
3 Replies Latest reply: Jun 27, 2012 12:32 PM by gdarling - oracle RSS

ODBC Connection to Oracle 11g

931534 Newbie
Currently Being Moderated
Hi, I am using Oracle 11g database. We have BI reporting tool and reporting purpose we need to connect to DB by ODBC connection.

I tried to create the ODBC connection in Control Panel --> Administration Tools --> Data Sources (ODBC)

I created the ODBC connection, but I am anable to connect to DB through this connection. I got ORA-12154 error.

I believe, I entered wrong values in ODBC connection. So please provide the which values need to enter in ODBC connection.

Please find below TNS Names and ODBC Values:

TNS NAMES:

SMDSP1=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=fc8racp1)
(PORT=1521)
)
(ADDRESS=
(PROTOCOL=TCP)
(HOST=fc8racp1n2-vip)
(PORT=1535)
)
(ADDRESS=
(PROTOCOL=TCP)
(HOST=fc8racp1n1-vip)
(PORT=1535)
)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=smdsp1.kayak.com)
)
)

ODBC CONNECTION:

1. Select the Driver -- Microsoft ODBC for Oracle

2. Data Soure Name -- ?
Description -- ?
User Name -- ?
Server -- ?

Please provide which values need to enter in point 2 from my TNS names.


Thanks & Regards,
Siri
  • 1. Re: ODBC Connection to Oracle 11g
    gdarling - oracle Expert
    Currently Being Moderated
    Data Source Name - fill in whatever you want.

    Server- fill in SMDSP1

    User Name is optional, but would be your Oracle database login.


    Greg
  • 2. Re: ODBC Connection to Oracle 11g
    931534 Newbie
    Currently Being Moderated
    Hi Greg,

    Thanks for your reply. I created the ODBC connection and tried to connect to DB, I got the following error:

    Connection Test Failed
    SQL##f - SqlState: 08001, ErrorCode: 12154, ErrorMsg: [Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS:could not resolve the connect identifier specified


    Please provide info, why I am getting this error.


    Thanks & Regards,
    Siri
  • 3. Re: ODBC Connection to Oracle 11g
    gdarling - oracle Expert
    Currently Being Moderated
    My assumption was that you are able to connect WITHOUT odbc (from sqlplus for example). Are you?

    My guess is that it may be a formatting problem with your tnsnames.ora, as I get 12154 when I try to use your entry as well. Try formatting it as per the docs (with leading white space) and see if that helps.

    http://docs.oracle.com/cd/B28359_01/network.111/b28317/tnsnames.htm

    Greg

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points