win8 x64 ,c#.net2010 ,oracle10g , office2007 , OracleConnection this Error "ORA-12154: TNS:could not resolve the connect identifier specified"?

Question

i've this code in project "Excel 2007 Addins"

 private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {
        string constring = "data source=localhost;user id=fi_archivedb;Password=fi_archivedb";
        OracleConnection con = new OracleConnection(constring);
        string sql = "select *from TBLFOLDERS"; 
        con.Open();
        OracleCommand cmd = new OracleCommand(sql, con);

        OracleDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
            MessageBox.Show(dr[0].ToString());
    }

Error=: ORA-12154: TNS:could not resolve the connect identifier specified

Also i've Same code in project windowsApplaction :

   private void Form1_Load(object sender, EventArgs e)
    {
        string constring = "data source=localhost;user id=fi_archivedb;Password=fi_archivedb";
        OracleConnection con = new OracleConnection(constring);
        string sql= "select *from TBLFOLDERS"; 
        con.Open();
        OracleCommand cmd = new OracleCommand(sql, con);

        OracleDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
            MessageBox.Show(dr[0].ToString());
    }

In this project "WindowsApplaction" the code correctly without any error.

Where's the problem?

Why does not the code in the project "Excel 2007 Addins"?

Answer 1

The TNS-xxxxx error points out that it is only an Oracle error.

Your connection string is not correctly set up. Oracle has it's own layer on top of I think any network protocol ever invented. It is named "TNS" for "Transparant Network Substrate". Using TNS (also known as "Oracle SQL*Net", also known as "Oracle Net"), you could in the dark ages transparently route packets from SNA to DECNet to IPX to IP based networks and back, without knowing anything about them. Currently, Oracle is switched to IP based only, but most users still use the old TNS layer.

To configure it, see for instance Oracle Net manual

Also, there are some recommendations to allow correct use of Unicode and easy use of a shared location using network files, please see 3.7.5.1.2 of configuration manual of Invantive product

In your case, since it sometimes seems to work, please check that you are comparing two identical installations, so use Excel 32-bit and Windows 32-bit. Or both 64-bit. Oracle has different DLL-s for the number of bits needed. And also different configuration files. As described in 2 you can share configuration files across multiple installation of Oracle SQL*Net using TNS_ADMIN in your registry.

Also note that especially with add-ins using VSTO it can be more complex to get an add-in that installs both into 32 and 64 bit Office environments to load the correct DLL always without hardcoding the versions and shipping the correct Oracle DLL-s with it. Also remember to use ODP, not the Microsoft variant. We've had initially major problems when we started in 2009 getting VSTO add-ins (Invantive Control for 32- and 64-bit Excel, some sheets required 64-bit to handle the full volume of data, please note limitations of .NET array in 4.0, consider 4.5) to run with any Oracle version installed (for instance 32-bit 10g or 11g might be encountered already installed) in combination with any Office version (why o why do people still run 32 bit....). The solution for that I can't share with you. Ultimately we have switched for smaller data volumes (< 4 GB in one request) to our own webservice which disentangles the client side from the server and which ensures that the user doesn't need to install 500 MB of Oracle client software. This might be a viable approach for you too.

Please note that there are others options too using Oracle SQL*Net, such as other directories with the available services as well as putting the following connect string and routing information in the .NET connection string.

Please note that you are missing a space between '' and 'from TBLFOLDERS' and that unnecessarily using a '' when you could suffice with just few fields can introduce performance issues (for instance due to an index not being selected even when it contained all necessary fields) and network bandwidth issues (dragging around a few KB extra on every roundtrip quickly increases the needed bandwidth).

A simple example of a tnsnames configuration file defining the service "XYZ.ACME.COM" or "XYZ":

# Include other file, new releases can nest them.
ifile=tnsnames-invantive.ora
# Direct in tnsnames.ora:
XYZ.ACME.COM=
( description =
  ( address_list =
    ( address = (protocol = tcp)(host = 192.1.1.1)(port = 1521)
    )
  )
  ( connect_data =
    (sid=XYZ)
    (global_name = XYZ.ACME.COM)
  )
)

Accompanying sqlnet.ora:

NAMES.DIRECTORY_PATH= (TNSNAMES)
NAMES.DEFAULT_DOMAIN=ACME.COM
DEFAULT_SDU_SIZE=65536
RECV_BUF_SIZE=1048576
SEND_BUF_SIZE=1048576

Did this fix your problem?