Oracle database TNS The value's length for key

Question

So I have a connection string to an Oracle Database essentially I copied it straight from sql developer and then cut out the white space.

I later programmatically add in the user name and password.The problem is that when I try to open an SQLConnection object. I get the error:

The value's length for key 'data source' exceeds it's limit of '128'

I don't really know much about oracle TNS connections. I used this connection string because it worked in the wizard when I pasted it in and did test connection. And what you see is essentially what Visual studio generated after I pasted in the TNS name.

Data Source="(DESCRIPTION=(ADDRESS= (PROTOCOL=TCP)
(HOST=qprd-scan.website.com)(PORT=3726))(CONNECT_DATA=(SERVER=dedicated 
(SERVICE_NAME=DBprd_developer)))";

This Data Source key is already around 160 characters.

I was looking at this post on the MSDN forums.

The guy basically says to chop out some of the other parts out of the data source and put them somewhere else. I am just not really sure how to do that.

The MSDN on connection strings doesn't really tell me much.

The MSDN on Connection strings and configuration files didn't help either.

Anyway I would be glad to show more of my code or stuff from app.config if asked

Answer 1

Here is what my app.config looks like for ODP.NET which I access via customized Enterprise Library (I converted data layer of EnterpriseLibrary5.0 from System.Data.OracleClient to ODP.NET's Oracle.DataAccess.Client. Note that publickey is my own produced one which is unique, and the version is again my own version number). Why did I customize it, well it's because System.Data.OracleClient cannot handle CLOB if it exceeds 32kb.

<configSections>
  <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.1.0.0, Culture=neutral, PublicKeyToken=4133a635bb2789db" requirePermission="true" />
</configSections>

<dataConfiguration defaultDatabase="DatabaseConnectionString" />
<connectionStrings>
    <add name="DatabaseConnectionString" connectionString="Data Source=TestDb;Persist Security Info=True;User ID=Usrname;Password=Pwd!;Max Pool Size=500;" providerName="Oracle.DataAccess.Client" />        
</connectionStrings>

And my TNS in tnsnames.ora file

TestDb=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=10.10.10.1)
      (PORT=1234)
    )
    (CONNECT_DATA=
      (SID=TestDb)
    )
  )

Hope that app.config and tnsnames.ora help you figure it out.

Answer 2

You haven't shown us the full code you use for connecting to a database, but it seems from a comment to another answer that you are using OLE DB. I would avoid using it, especially if it seems to have a somewhat arbitrary limit of 128 characters for a data source.

I would also point out that you can also avoid having to install an Oracle client, as recommended by another answerer. I don't have much experience with the 'instant' client, but the full client is a hefty download and isn't necessary just to be able to connect a C# program to Oracle.

Instead, we can use the Oracle Managed Data Access library. You can install this using NuGet. To do this:

• go to Tools > Library Package Manager > Package Manager Console,
• ensure that the correct project is chosen in the 'Default project' drop-down list,

• enter

  Install-Package odp.net.managed
  

This should add download the library from NuGet and add Oracle.ManagedDataAccess to the References of your project.

Then, if you add a using directive for Oracle.ManagedDataAccess.Client, the following code should talk to an Oracle database:

string connStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<service_name>)));User Id=<user>;Password=<password>";
Console.WriteLine("Connection string has length " + connStr.Length);
using (var connection = new OracleConnection() { ConnectionString = connStr })
{
    connection.Open();
    OracleCommand command = new OracleCommand("SELECT * FROM DUAL", connection);
    using (OracleDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader.GetString(0));
        }
    }
}