7 Replies Latest reply: Apr 22, 2010 10:11 AM by user8609285 RSS

    ORA-12154 using Oracle.DataAccess.Client

    user8609285
      I am experiencing an ORA-12154 exception when accessing a remote Oracle database using ADO.NET called from c# code running on a Windows 2003 system. The code functions correctly on our internal (remote) database using the Oracle XE 10.2 Client. At the customer site with Oracle 10.1 Client the same code generates the 12154 error.

      At the customer site, I can connect without errors with the customer's connection information (Hostname, port, SID) using Navicat 8 Lite for Oracle and SQL-Plus. I can also connect with no problems using ODBC (except I can't use ODBC because it causes other problems when accessed from ASP.NET).

      The connection string is correctly formulated i.e. "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myServerName)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myServiceName)));User Id=myUserName;Password=myPassword"

      When I run TNSPING on the client's system I get the following result (server and SID name altered for confidentiality reasons):

      Adapter TNSNAMES zur Auflösung des Alias benutzt
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myServerName)(PORT = 1521)) (CONNECT_DATA = (SERVER = dedicated) (SERVICE_NAME = myServiceName)))
      OK (10 ms)

      I can't determine anything about the listener since I don't have access to teh datbase server.

      To summarize, my .net code fails when I use the Oracle Data Access Client, but works fine with ODBC, and Navicat, SQL Plus also work fine.

      Can anyone suggest the cause and possible solution for this problem? - thanks in advance for any help :)
        • 1. Re: ORA-12154 using Oracle.DataAccess.Client
          EdStevens
          user8609285 wrote:
          I am experiencing an ORA-12154 exception when accessing a remote Oracle database using ADO.NET called from c# code running on a Windows 2003 system. The code functions correctly on our internal (remote) database using the Oracle XE 10.2 Client. At the customer site with Oracle 10.1 Client the same code generates the 12154 error.

          At the customer site, I can connect without errors with the customer's connection information (Hostname, port, SID) using Navicat 8 Lite for Oracle and SQL-Plus. I can also connect with no problems using ODBC (except I can't use ODBC because it causes other problems when accessed from ASP.NET).

          The connection string is correctly formulated i.e. "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myServerName)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myServiceName)));User Id=myUserName;Password=myPassword"

          When I run TNSPING on the client's system I get the following result (server and SID name altered for confidentiality reasons):

          Adapter TNSNAMES zur Auflösung des Alias benutzt
          Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myServerName)(PORT = 1521)) (CONNECT_DATA = (SERVER = dedicated) (SERVICE_NAME = myServiceName)))
          OK (10 ms)

          I can't determine anything about the listener since I don't have access to teh datbase server.

          To summarize, my .net code fails when I use the Oracle Data Access Client, but works fine with ODBC, and Navicat, SQL Plus also work fine.

          Can anyone suggest the cause and possible solution for this problem? - thanks in advance for any help :)
          ora-12154 means one thing and one thing only.
          [oracle@vmlnx01 ~]$ oerr ora 12154
          12154, 00000, "TNS:could not resolve the connect identifier specified"
          // *Cause:  A connection to a database or other service was requested using
          // a connect identifier, and the connect identifier specified could not
          // be resolved into a connect descriptor using one of the naming methods
          // configured. For example, if the type of connect identifier used was a
          // net service name then the net service name could not be found in a
          // naming method repository, or the repository could not be
          // located or reached.
          // *Action:
          //   - If you are using local naming (TNSNAMES.ORA file):
          //      - Make sure that "TNSNAMES" is listed as one of the values of the
          //        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
          //        (SQLNET.ORA)
          //      - Verify that a TNSNAMES.ORA file exists and is in the proper
          //        directory and is accessible.
          //      - Check that the net service name used as the connect identifier
          //        exists in the TNSNAMES.ORA file.
          //      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
          //        file.  Look for unmatched parentheses or stray characters. Errors
          //        in a TNSNAMES.ORA file may make it unusable.
          //   - If you are using directory naming:
          //      - Verify that "LDAP" is listed as one of the values of the
          //        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
          //        (SQLNET.ORA).
          //      - Verify that the LDAP directory server is up and that it is
          //        accessible.
          //      - Verify that the net service name or database name used as the
          //        connect identifier is configured in the directory.
          //      - Verify that the default context being used is correct by
          //        specifying a fully qualified net service name or a full LDAP DN
          //        as the connect identifier
          //   - If you are using easy connect naming:
          //      - Verify that "EZCONNECT" is listed as one of the values of the
          //        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
          //        (SQLNET.ORA).
          //      - Make sure the host, port and service name specified
          //        are correct.
          //      - Try enclosing the connect identifier in quote marks.
          //
          //   See the Oracle Net Services Administrators Guide or the Oracle
          //   operating system specific guide for more information on naming.
          Bottom line is the net service name you presented is not found in the tnsnames that was being used. Which may not be the tnsnames you think was being used.

          And since it never resolved that on the client side, there is no need to look to anything (like the listener) on the host side. You never got out of your driveway, so there is no need to see if the entrance to your destination is open.
          • 2. Re: ORA-12154 using Oracle.DataAccess.Client
            19426
            I'm not very familiar with this stuff,but as fas as I know ODAC runs in its own ORACLE_HOME. So it does not read tnsnames.ora,which works for the other components you mention.
            You may set environment variable TNS_ADMIN, which points to the 'other' %ORACLE_HOME%\network\admin.

            Werner
            • 3. Re: ORA-12154 using Oracle.DataAccess.Client
              user8609285
              Thanks for the quick reply - I will check thse points.
              • 4. Re: ORA-12154 using Oracle.DataAccess.Client
                user8609285
                Thanks for the quick reply - I will try your suggestion.
                • 5. Re: ORA-12154 using Oracle.DataAccess.Client
                  user8609285
                  I tried your suggestion - unfortunately I still cannot connect, but I no longer seem to get the error ORA-12154 message. The application seems to hang, without generating an exception...
                  • 6. Re: ORA-12154 using Oracle.DataAccess.Client
                    sb92075
                    I can't determine anything about the listener since I don't have access to teh datbase server.
                    Makes troubleshooting more challenging
                    To summarize, my .net code fails when I use the Oracle Data Access Client, but works fine with ODBC,
                    So problem is strictly with .NET code, since DB responds correctly when accessed by valid & correct client.
                    • 7. Re: ORA-12154 using Oracle.DataAccess.Client
                      user8609285
                      I gave in and installed the OracleXEClient on the system - the problem is solved, I get a connection instantly. I only wish I knew why it works where the normal Oracle 10.1 Client doesn't :(