3 Replies Latest reply: Jun 27, 2012 2:32 PM by gdarling - oracle RSS

    ODBC Connection to Oracle 11g

    931534
      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
          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
            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
              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