1 Reply Latest reply: Sep 12, 2012 4:03 PM by 957028 RSS

    Exception occuring when connecting to 11g database using .Net 10g ODAC

    957028
      Hi:

      We've got a 32 bit .Net windows application which uses ODAC 10g (1.x) to connect to a 10g database on the backend.
      Things work fine in this deployment.

      We're in the process of setting up a new development machine.
      This machine has ORACLE 11g (64 bit) installed on it.
      We're running into issues connecting the the database.

      This is the code block we're using to connect to the database:
      =======================================
      string dataString = "user id=xxx;password=\"xxx\";data source=localhost:1521/ORCL";
      using (OracleConnection oraconn = new OracleConnection(dataConn))
      {
      oraconn.Open();

      Oracle.DataAccess.Client.OracleCommand cmd = new OracleCommand(sOraSQL, oraconn);
      cmd.CommandType = CommandType.Text;
      Oracle.DataAccess.Client.OracleDataAdapter da = new OracleDataAdapter(cmd);

      DataTable dt = new DataTable();
      da.Fill(dt);

      oraconn.Close();
      return dt;
      }

      We get an exception on the "oraconn.Open()" command.

      The output window in VS2010 shows this:
      -----------------------------------------------------------
      A first chance exception of type 'Oracle.DataAccess.Client.OracleException' occurred in Oracle.DataAccess.dll


      The oraconn variable looks like:
      -------------------------------------------
      - oraconn {Oracle.DataAccess.Client.OracleConnection} Oracle.DataAccess.Client.OracleConnection
      + base {Oracle.DataAccess.Client.OracleConnection} System.ComponentModel.Component {Oracle.DataAccess.Client.OracleConnection}
      ConnectionString "user id=xxxxx;password=\"xxxxxx\";data source=localhost:1521/ORCL" string
      ConnectionTimeout 15 int
      DataSource "localhost:1521/ORCL" string
      - ServerVersion 'oraconn.ServerVersion' threw an exception of type 'System.InvalidOperationException' string {System.InvalidOperationException}
      - base {"Operation is not valid due to the current state of the object."} System.SystemException {System.InvalidOperationException}
      - base {"Operation is not valid due to the current state of the object."} System.Exception {System.InvalidOperationException}
      + Data {System.Collections.ListDictionaryInternal} System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
      HelpLink null string
      + InnerException null System.Exception
      Message "Operation is not valid due to the current state of the object." string
      Source "Oracle.DataAccess" string
      StackTrace " at Oracle.DataAccess.Client.OracleConnection.get_ServerVersion()" string
      + TargetSite {System.String get_ServerVersion()} System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}
      + Static members
      + Non-Public members
      State Closed System.Data.ConnectionState
      + Static members
      + Non-Public members

      The details of the exception are:
      ---------------------------------------------
      - ex {""} System.Exception {Oracle.DataAccess.Client.OracleException}
      + [Oracle.DataAccess.Client.OracleException] {""} Oracle.DataAccess.Client.OracleException
      + Data {System.Collections.ListDictionaryInternal} System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
      HelpLink null string
      - InnerException null System.Exception
      + Static members
      Message "" string
      + Source 'ex.Source' threw an exception of type 'System.NullReferenceException' string {System.NullReferenceException}
      StackTrace " at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)\r\n at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src)\r\n at Oracle.DataAccess.Client.OracleConnection.Open()\r\n at Saw.Classes.DataUtils.ExecuteQuery(String dataConn, String sOraSQL, CommandType commandType) in C:\\CEP Development\\Workstation\\trunk\\AnalystWorkstation\\Classes\\DataUtils.cs:line 113" string
      + TargetSite {Void HandleErrorHelper(Int32, Oracle.DataAccess.Client.OracleConnection, IntPtr, Oracle.DataAccess.Client.OpoSqlValCtx*, System.Object, System.String)} System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}
      + Static members
      + Non-Public members



      To reiterate, in our original deployment (10g database with 10g (1.x) ODAC, we can connect fine with the database.
      It's with the new setup (11g database with 10g(1.x) ODAC) that we are having issues.
      We've previously been able to deploy on client machines which were using ORACLE 11g.
      To accomplish this, we had to make sure that the password was inclosed in quotes (").


      What is going on here?
      What should we be looking at?
      How do we go about debugging fixing this?

      Thanks,
      JohnB
        • 1. Re: Exception occuring when connecting to 11g database using .Net 10g ODAC
          957028
          OK. We are now seeing some new behaviour.
          I've installed ODAC 11g 32 bit onto the problem machine.
          I also installed onto the system where things work.

          The result is:
          -----------------
          1) On the machine where everything worked with 10g ODAC, it's working fine with 11g

          2) On the machine where it DID NOT work with 10g ODAC, it still DOES NOT WORK.



          Using the 11g 32 bit ODAC, we now get the following exception:
          ------------------------------------------------------------------------------------------
          ora-12514: TNS: listener does not currently know of service requested in connect descriptor

          We are able to run SQLDeveloper and point to the same SID (ORCL) and we can connect to the DB instance fine.



          Here is what the TNSNAMES.ORA file looks like:
          -----------------------------------------------------------------
          # tnsnames.ora Network Configuration File: C:\app\DjM\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
          # Generated by Oracle configuration tools.

          LISTENER_ORCL =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


          ORACLR_CONNECTION_DATA =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          )
          (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
          )
          )

          ORCL =
          (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
          (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl.168.1.100)
          )
          )

          This is what the SQLNET.ORA file looks like:
          -----------------------------------------------------------

          # This file is actually generated by netca. But if customers choose to
          # install "Software Only", this file wont exist and without the native
          # authentication, they will not be able to connect to the database on NT.

          SQLNET.AUTHENTICATION_SERVICES = (NTS)

          This is what the connection string looks like:
          ------------------------------------------------------------
          "user id=xxx;password=\"xxx\";data source=localhost:1521/ORCL"


          Again, any and all help/suggestions would be appreciated here.

          Thanks,
          JB