C# Connection String for Oracle database is not working

Is the app 32Bit or 6Bit?  Did you download the 32Bit or 64Bit Oracle?

Anyway, you downloaded the wrong thing.  Oracle XE is a complete database server.  All you need is the Data Access Providers.

If you are using .Net, I would strongly suggest the ODP.Net Managed Drivers.  You don't need an Oracle Client install.

If you want to continue using OleDB, then you need the client install.

You also don't need the tnsnames.ora file.  You can use the EZConnect syntax.

You can get the drivers here:
http://www.oracle.com/technetwork/topics/dotnet/downloads/net-downloads-160392.html

Remember to download the correct Bit version for your app.

There are tons of examples in the online docs and web on EZConnect syntax.

Thanks for the update , I am running service on a 64bit windows server 2012R2 via IIS8.  I need to use OLE so I downloaded through NUGet the 64bit Oracle.ManagedDataAccess however since doing this I am getting an error when I attempt to update the service reference to my client I get the following error.  

"could not load file or assembly 'Oracle.DataAccess"'


Have you come across this before ? Even if I uninstall the Nuget Package the error remains.

regards
Pat

>> I am running service on a 64bit windows server 2012R2 via IIS8.

The OS Bit version doesn't matter.  What is the Bit version of the app?  Also, don't compile for AnyCPU.  Pick one.

>>I downloaded through NUGet the 64bit Oracle.ManagedDataAccess

That is ODP.Net not OleDB.  If you have to use OleDB then you need to download and install the ODAC that contains the OleDB drivers.

After downloading and unzipping, do not just clike o nthe install.bat file.  You must run it from a CMD prompt because it has options you need to select.

Also, don't compile for AnyCPU.  Pick one.

I hope by this slightvw means that you need to pick one because Oracle is compiled against a particular platform. Generally speaking, compiling against "Any CPU" is exactly what you should be doing. When dealing with Oracle, there is an exception to this.

When dealing with Oracle drivers and .Net, I've found that things get confused with a .Net app compiled with AnyCPU.

Well, yeah. With Oracle you have to do a specific architecture. This is because the client has architecture-specific code used internally. I know that the client does, anyway; I'm not sure if the managed client works differently.

Hi All,
whilst I have made some progress in getting rid of the  "could not load file or assembly 'Oracle.DataAccess" error , I am still having issues.  The following is what I have done

1. I installed ODAC on the Windows 2012 server and executed bat file
2. Set the project to 64 bit platform
3. I disabled 32bit processing on IIS application pools
4. Installed 64bit drivers

when I look at the 64bit drivers on the 2012r2 server the only oracle driver that exists is Oracle XE 11.02.00.02 (see attachment)
When I issue the SQL statement "select * from tas_soh" I can see that the application is successfully reading the file. However when I try to select a field name or by field name (eg. select * from tas_soh where tas_shop_order  = '178382' ) I get the following error  " ORA-00904: "TAS_SHOP_ORDER": invalid identifier

I know that the field name tas_shop_order is correct  and I have tried all sorts of variations without success (eg  "tas_shop_order"    & tas_soh.tas_shop_order & "tas_soh.tas_shop_order").  I cannot understand why the field names cannot be recognised. Could it be down to driver ???

appreciate any help you can give me

regards
Pat
Doc5.docx

Why would you expect to find an OleDB drvier in the ODBC Administrator program?

Do you want the ODBC drivers or OleDB drivers?  They are two separate things.

The XE isn't what you want.  That came with the Oracle XE database install.  When you installed the ODAC download did you install the ODBC drivers?

If you want the ODBC drivers, did you download the ODAC with the ODBC drivers?

>> Could it be down to driver ???

I doubt it.

How are you verifying that the column exists in the database you are connecting to?  You may not be connecting to the database you think you are or the user you are using might not have access to that column.

Yes I did down load ODAC with all components and I do want to use the OLEDB drivers. At the end of the setup there was a list of approx. 20 SQL scripts which need to be run however I am not sure whether these are required for the drivers to be correctly installed. I have attached the list of SQL's - do I need to run these ? If not then where can I verify that the Oracle OLE drivers have been installed ?

regards
Pat
Doc1.docx

I've never seen any of those.  So, no, don't run them.

Exactly which file did you download and what steps did you follow to install the OleDB drivers?

I see in the image you are looking in the client_2 folder under an 11gR2 home.  If you downloaded the latest ODAC, it is 12c so you are looking in the wrong place.

My guess is you have installed so many things that you have a lot more installed than you need.  I would remove everything that you don't need and start over with only what you do.

For example, unless you need a database on your machine, you don't need XE.  I have no idea what you installed in the client_2 folder so you may not need that either but I cannot say for sure.

After I uninstall existing XE and  ODAC versions and re-install ODAC 12c  then How can I confirm that the Ole drivers have been installed. Surely there is some way of verifying whether the drivers exist on the server ?

All previous Oracle versions un-installed and  64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64  - Installed
Below I have documented 3 different attempts to connect to the oracle database:

1st ATTEMPT using OLEDB Connection
       
       // Default to Ole
       OleDbConnection conn = new OleDbConnection(connString);
       OleDbCommand cmd = new OleDbCommand(sqlStmt, conn);
       conn.Open();
      .
      .
      .

      // Fill a DataSet
       ds.Reset();        
       Adapter.Fill(ds);

OLE Connection string:
---------------------------------
Provider=OraOLEDB.Oracle;Data Source=TEST1;User Id=USER1; Password=pass1;

RESULT
-----------
The 'OraOLEDB.Oracle' provider is not registered on the local machine. / System.Collections.ListDictionaryInternal / SQL Stmt: select * from tas_soh where tas_shop_order  = '178382'


I assumed that OraOLEDB.Oracle was loaded when ODAC was installed ?????


      .
2nd  ATTEMPT using ODBC Connection

       // if odbc connection
       OdbcConnection  conn = new OdbcConnection(connString);
       OdbcCommand cmd = new OdbcCommand(sqlStmt, conn);
       conn.Open();
      .
      .
      .

      // Fill a DataSet
       ds.Reset();        
      Adapter.Fill(ds);


ODBC Connection string:
------------------------------------
Driver={Oracle in OraClient12_Home 1}; Dbq=TEST1;Uid=USER1;Pwd=pass1;

RESULT
-----------
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified / System.Collections.ListDictionaryInternal / SQL Stmt: select * from tas_soh where tas_shop_order  = '178382'


With the ODBC connection I have successfully tested the connection manually via ODBC Data Source Administrator (64 bit)




3rd ATTEMPT using Oracle Connection


Finally I tried connecting with the following

       // Oracle Connection
       OracleConnection conn = new OracleConnection(connString);
       IracleCommand cmd = new OracleCommand(sqlStmt, conn);
       conn.Open();
.
.
      // Fill a DataSet
       ds.Reset();        
      Adapter.Fill(ds);

Connection String:
--------------------------
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host = xxxxxxxx)(Port = 10001)))(CONNECT_DATA=(SERVICE_NAME=TEST1)));User Id=USER1;Password=pass1;

RESULT
-----------
This fails on the conn.Open(); statement with the following error:

connection.serverversion' threw an exception of type 'system.invalidoperationexception' oracle


Any idea why all 3 are failing to access the Oracle database ?

Regards
Pat

If you do not answer my questions I don't see how I can help you.

Exactly which file did you download and what steps did you follow to install the OleDB drivers?

By your description, you downloaded this file:  ODAC121024_x64.zip

Now, exactly how id you install it?

>>Surely there is some way of verifying whether the drivers exist on the server ?

I've never had to physically check but I would assume it is somewhere in the registry.

Sorry, see answers to your questions belowl
Exactly which file did you download and what steps did you follow to install the OleDB drivers?

Downloaded  ODAC121024_x64.zip - 287 MB (301,348,751 bytes). Clicked on setup.exe  and stepped through the installation selecting everything for download. There was no special setup for oledb drivers



I see in the image you are looking in the client_2 folder under an 11gR2 home.  If you downloaded the latest ODAC, it is 12c so you are looking in the wrong place.


Yes, I had many attempts at installing different versions. However before above install I un-installed prior versions as you requested.

 
 
regards
Pat

Latest Update:

I Decided to take your advice , bite the bullet and go down the ODP.NET managed driver route for oracle connections rather than using oledb. Whilst making this change I think I found out why the connection has been failing. The application is using dataAdaptors to enable the data read via sql commands to be read directly into a dataset.  I needed to change the Adapter to be an OracleDataAdapter.

So I am now successfully reading the datafiles from Oracle however I have one outstanding issue.

When I specify a query like Select * from TAS_SOH   I get:

SOH_Unique_ID    SOH_Shop_Order    SOH_Item     SOH_Lot_Code
123456                              112233              F552112F     ZJP1132
123457                              112233              F552112G    ZJP1133
etc...

However if I change the query to Select  SOH_Shop_Order   from TAS_SOH   I get an error message ORA-00904 "SOH_UNIQUE_ID": invalid identifier


If I change the query to  any of the following:

      Select  "SOH_Shop_Order"   from TAS_SOH
or   Select  TAS_SOH.SOH_Shop_Order   from TAS_SOH
or   Select  "TAS_SOH.SOH_Shop_Order"   from TAS_SOH

I continue to get the same ORA-00904 error message.  Why can I select by * but not reference by field name ?? I've never come across anything like this before

regards
Pat

>>However if I change the query to Select  SOH_Shop_Order   from TAS_SOH   I get an error message ORA-00904 "SOH_UNIQUE_ID": invalid identifier

I'm not sure how a select of the shop_order column would generate an error on the unique_id column.

>>I've never come across anything like this before

If someone created the objects using double quotes, then they are now case sensitive and you must ALWAYS use double quotes around them.  The case must match exactly.

If they did that, they don't understand Oracle and I would try and convince them to undo that mistake.  It only causes problems...

It is also possible to have spaces in the column names if you double quote the names.  My guess is there are spaces in there not underscores.

Instead of using code to check the objects, use sqlplus or SQL Developer and connect directly to the database.  Then you can describe the table and not have to guess.

I am in the process of downloading SQL developer. However this relates not only to custom tables. I ran an SQL across a system table Select * from user_tab_columns  and the details were successfully returned. Then I selected some of the fields returned and changed the sql to  Select table_name,column_name,data_type,data_length from user_tab_columns   and again I am successfully displayed the 4 fields. One of the table names returned is the above display is "TAS_E3_Despartment so I enter a new sql statement
Select table_name,column_name,data_type,data_length from user_tab_columns  where table_name ='TAS_E3_Despartment' - no error but also no records returned. Even when I change this to Select table_name,column_name,data_type,data_length from user_tab_columns  where table_name LIKE '%TAS%' - still nothing returned. Finally if I change it to  Select table_name,column_name,data_type,data_length from user_tab_columns  where table_name LIKE '%T%' - It works !!!

How bizarre is this -  

 I notice that my oraConn object (oracle connection) has the following exceptions:

• Instance Branch
• Server Version
• SessionId
• M_MajorVersion
• M_MinorVersion

And My OracleCommand object  cmd has the following error

• ImplicitRefCursors

And my OracleDataAdaptor object oraAdapter has the following error

• SafeMapping

it crashes out with ORA-00904 error after attempting oraAdapter.Fill(ds)

below is the  code that I am using to test the oracle connection. It still does not allow for fields to be selected. This just does not make any sense.

                        try
                        {
                            //
                            // read record from shop order header
                            //  
                            string connString = "user id=user123;password=pass123;data source=TEST1";
                            string sqlStmt = "SELECT * FROM tas_soh where tas_shop_order  = '178382'";

                           OracleConnection oraConn = new OracleConnection(connString);
                            OracleDataAdapter oraDataAdapter = new OracleDataAdapter(sqlStmt, connString);
                            OracleCommand oraCmd = new OracleCommand(sqlStmt, oraConn);
                           
                            oraConn.Open();

                            DataSet ds = new DataSet();
                            oraDataAdapter.Fill(ds);
                            int noOfRowsDs = ds.Tables[0].Rows.Count;
                            int noOfColsDs = ds.Tables[0].Columns.Count;
                            int i = 0;
                            foreach (DataTable table in ds.Tables)
                                foreach (DataRow row in table.Rows)
                                {
                                    string valu = row.ItemArray[0].ToString().Trim();
                                }
                        }
                        catch (Exception ex)
                        {
                            g.MyExe.catchNo = "040055";
                            g.MyExe.thisSourceMethod = MethodBase.GetCurrentMethod().DeclaringType.Name + "/" + MethodBase.GetCurrentMethod().Name;
                            g.MyExe.errorData = ex.Message.ToString() + " / " + ex.Data.ToString();
                            g.MyExe.RecordError();
                        }

Everything points to case sensitivity
You were spot on about case sensitivity. Once I changed the query from "select * from tas_soh where soh_shop_order ='178382'" to select * from tas_soh where "SOH_Shop_Order" ='178382' it recognises the field names.  I have to say that  oracle databas seems far more unforgiving than SQL.

Use Bind variables.
I didn't know about bind variables until you mentioned them. I looked them up and there seems to be very significant savings. I will certainly consider using these in future.

I don't see where you are using fields?

I think it is just my terminology. My background is AS400 where tables are file and columns are fields

Any reason you are using a local dataadapter and dataset?  I rarely have a need to use them.
I often need to manipulate the data read prior to binding to a gridview. I find reading the data directly into a dataset via the DataAdaptor is the best way I have found to do this. If there is a better way then I am always open to change.

Thanks for all of you help on this, I really do appreciate it. I will leave this question open in case you have any comments on the above and I'll close it in a couple of days giving you the points.

regards
Pat

>>I have to say that  oracle databas seems far more unforgiving than SQL.

Only when the DBA or whomever created the objects screws things up.  NEVER use double quotes on Oracle object names.  The default is case insensitive.  Someone, not Oracle, did that on purpose.

>> If there is a better way then I am always open to change.

I believe you can set the dataset directly using the data reader.  I'm not where I can access my code but going from memory this should work:

ds = oraCmd.ExecuteReader;

I can confirm this tomorrow or Tuesday when I get back to where my code is.

>>I often need to manipulate the data read prior to binding to a gridview

In what ways are you manipulating the data?  Many times you can do what you need at the database layer where the server has more horsepower and can do it better and faster.

Then you can bind the gridview from the reader and eliminate the need to store the data locally.  The data goes straight form the database to presentation.

Many thanks for your perseverance.