Oracle XE, ODP.NET and Oracle 9i in the same Windows XP machine

English: The logo of Oracle Corporation de:Bild:Oracle-Logo.svg he:תמונה:Oracle Logo.jpg (Photo credit: Wikipedia)

Saturday night, 23-Aug-2014

For a couple of weeks now, I have been figuring out how to make Oracle 9i and Oracle XE work in the same machine. For a long time, I have been running Oracle 9i and ODP.NET, designing, developing and maintaining applications using the Oracle RDBMS. Only lately when I started working in a new company that I am having restricted access to databases that I had to resort to some local DB, even on my old XP machine. I have the disc space to begin with.

And having some access on some Oracle dB means I had to also create and maintain a local dB that is Oracle-based. In comes Oracle XE.

That is when I started experimenting with Oracle Express Edition, and with 11g as the latest, and with ODP.NET version of 10.x.x, did not think of having a compatibility issue, even then, since I was doing all these in a Windows XP-based unit.

Then I got the problem. I mean, I don’t know how it happened, but for a moment, after initially installing oracle XE, I was getting both local dB and data warehouse dB (using TNS) to work. I was very happy. And as in all installs, I did a reboot. And if in most cases, a reboot would rebuild and repair and renew settings and realign once again everything – as new – something now doesn’t work. I either would get the DW dB to be working, and Oracle XE dB to be broken, or vice-versa.

I did many things, like tweaking the Path variables, the 3 .ora files, even the TNS_ADMIN variable and value, pointing either to the Oracle 9i directory, or the Oracle XE, and like I said, I would either get one or the other, but not both. And in most cases, I get the DW dB which is using TNS, to be the one working. Doing a PING to XE would result in error, but TNSPING to XE would give a positive result. But both PING and TNSPING to 127.0.0.1 would give both a positive result.

As in previous issues and problems, I did a lot of searching. And this becomes complicated, because I have to wait until I get home then I will be able to look up ORA-12154, ORA-12514, ORA-12528, etc., etc., because internet access is also restricted! Bummer!

There were some suggestions that focused on setting up, if not already, TNS_ADMIN variable in the system variables, and assigning its value to the Oracle XE …\network\admin path, and I tried this one, adding, removing, changing, then putting it back to Oracle 9i …\network\admin path, and still, I would either get one or the other, but not both. And still the same, I would usually be having the DW dB working, but not the Oracle XE dB.

I should mention that for each trial, I am either uninstalling Oracle XE, at least, or I would uninstall all of Oracle XE, ODP.NET and Oracle 9i, in sequence. And in reinstalling, it is also in the same order: Oracle XE, then ODP.NET, and finally, Oracle 9i. I believe it is common knowledge for all who have tried it, that for ODP.NET to work with Oracle 9i, the installation should be in that sequence. This is more or less something to do with the final value of Path in the system variables, and even in the default Home selection for Oracle.

Also, after each uninstallation, I would run CCleaner to remove invalid registry items, after a reboot. And after running CCleaner, another reboot, to really be sure that I am starting clean.

After many trials and errors, I made it to work. I did a reboot, and I checked, and it is still working. Another reboot, and it is still working. I run CCleaner, to once again remove invalid registry items, then a reboot, and yes, I confirm that both data warehouse dB using TNS and Oracle XE dB are both working.

Monday morning, 25-Aug, when I came to work, I booted up my PC, tested the 2 dBs, and they are working. I did a reboot, just to be really sure that I am not dreaming, and yes, both are working.

What did I do to make it work?

1. I removed the TNS_ADMIN setting from the System Variables. So it is now the individual .ora files being looked up and evaluated when I do a connection to each dB. (I think).
2. After the installation, I did not touch the .ora files, which means that whatever value that was created by the installers, then that was it. No tweaking or whatever.
3. The ODP.NET …\network\admin folder does not contain any .ora file. That’s a fact. It is just the dll file anyway that is required by .NET applications. Nada mas.

The day is now closing, and I have done quite a number of reboots – I am just very, very curious if the working condition will be sustained throughout the day. And yes, even as the day is already closing, I still get both data warehouse dB using TNS, and the local dB, using Oracle XE. I guess some times, you just keep your hands off things, and they will work on their own. They don’t need our help. 

Sometimes.

Till then!

Related articles

• oracle connection String with C# using oledb
• Fully freeware APEX environment I: CentOS & Oracle XE 11g
• Connecting to Oracle XE Database from Java - JDBC
• Oracle XE - Uninstall causes Windows Event Log to Stop
• Installing Oracle 11g Release 2 Express Edition in Ubuntu 13.04 (Unsupported OS)
• Oracle Import Saples
• Install Oracle XE 11g on Windows 7 64-bit
• Connecting Java program to Oracle10g XE
• Oracle XE Fail Over Configuration
• Oracle APEX Video Training Tutorials - Complete List

The provider is not compatible with the version of Oracle client; ORA-03134

Image via Wikipedia02-Dec-2011


One of the final touches that I had to do on the new refurbished PC that I got is to make it into a local web server, an intranet application per se. And having found that in Windows 7, the way to go is publishing by a compiled site, I did just that.

I am one who would tend to make use of the different technologies and methodologies, just to be able to familiarize and not be tied to one; it’s for the good: when that singular method you lean on breaks, your whole system breaks, so having many sticks to lean on, you still are lifted up by the many others that remain standing and strong – when one breaks or falters.

Here’s what happened:

I copied over the codes, built the solution, and then published it. Since this is the second time around, I was going through all these things, including the creation of the folder, a breeze.

When I accessed the service from another PC, well, I got a 404 error.

I went to the IIS manager, and ticked all the services and features that I think are needed; of course, I haven’t memorized these sets, so I referenced the other PC that is already running the same web application.

That wasn’t enough. I still get a 404 error.

I remembered other things needed, so I didn’t hesitate downloading Web Platform Installer, where some other small, small services or features, not to mention some other service packs or applications, are offered.

I did that, and when the installation was finished, I did a reboot.

That brought the intranet application up and running!

Well, not all of it.

The section that runs queries using Oledb was okay. The other section that runs queries using Oracle client (ODP.NET) wasn’t. I got this error message:

“The provider is not compatible with the version of Oracle client”


Of course, the web being my primary source of information, I immediately launched Maxthon, which I prefer using to IE (a snail browser; Netscape is many times faster than IE), and searched on solutions to this problem at hand.

Again, many, many articles and discussions offered various steps to solve this problem, like copying over some dll files to the folder holding the exe, installing the ‘right’ and ‘compatible’ version of Oracle client, etc., etc. I could say the list is endless. I think I also saw some that suggested checking on the Oracle home definition, in various way and means, which I did by invoking Home Selector, and finally, through the registry editor.

And these didn’t help me. My problem isn’t solved. I actually did another comparison technique, that of the working PC and the non-working PC. They are the same.

But taking from some from the whole, I did what I think will help. I reinstalled ODP.NET, of course the version I am using is the same all over the computers that I manage. So there is no question of introducing an ‘incompatible’ or ‘incorrect’ version for this software.

Well, that fixed the problem on the particular section. BUT, it broke a number of other applications!
In particular, I got this error:ORA-03134. This is something about a newer Oracle client version accessing an older Oracle server version.

I’ve been through this one, but I don’t remember how I fixed it! My gracious me…

But I thought, since I fixed one by reinstalling, why don’t I do the same with this other?

And I did just that, reinstalling Oracle 9i client – which fixed ORA-03134.

I did a reboot, and after confirming that all the sections of my just-released web application are working, I retired – for the day.

Here’s a summary of what I was doing, what happened, and how problems are fixed, so far:

1. Publish an intranet application on a local desktop PC running in Windows 7
2. Copy the codes from another PC (already running the same web apps).
3. Created the folders in the Program Files directory for the web apps destination.
4. Built, and published to local folder (through compiled site method).
5. Opened IIS manager (inetmgr), then added in the Virtual Directory (PC_Name >Sites > Default Web Site right click, then select Add Virtual Directory).
6. Once the Virtual Directory is added, I right click on it, and then selected Convert to Application.
7. That’s when error 404 was thrown out.
8. Turned on additional windows features on IIS. Still got 404.
9. Installed Web Platform Installer, followed by installing the service pack, and some auxiliary services and features.
10. Reboot, and that’s when Web Apps was brought up, but section using Oracle client wasn’t working, with this error message: The provider is not compatible with the version of Oracle client.
11. Reinstalled ODP.NET (the version I am using is ODT with ODAC 11.2.0.2.1)
12. Fixed section using Oracle client for oracle connection, but created a problem for the others, specifically Oracle 9i client, with this error message: ORA-03134.
13. Reinstalled Oracle 9i.
14. The other programs were okay, and every section of the web apps works.
15. Reboot, and crossed my fingers while waiting for the machine to come back up.
16. A final check and review, and everything were working fine.
17. And so I retired for the day, tired, but happy.

Till then!

Related articles

• Remote ORACLE server connection string (stackoverflow.com)
• Got Oracle? Confused over Licensing? Ask an Oracle License Expert! (prweb.com)
• ODP.NET and VS2010 (mycompuquest.blogspot.com)
• Latest Version of the Standby Wizard for Oracle Hailed as Best Standby Database Solution for Oracle RDBMS High-Availability. (prweb.com)
• Oracle Database in Oracle Linux Cannot Start / Open (pandazen.wordpress.com)
• Alight Planning Announces Oracle Gold Partnership (prweb.com)
• Cintra Named Oracle PartnerNetwork Specialized Global Partner of the Year 2011 for Database (prweb.com)
• ORA-28547 using xcopy ODP.NET and instant client (stackoverflow.com)
• Install Oracle patch set 4 and bundle 10 on RAC environment (shareitexpertise.wordpress.com)
• Oracle VP talks challenges of the public cloud model (venturebeat.com)

ODP.NET and VS2010

For the past couple of weeks, I have been struggling with the usage of ODP.NET 11g version, together with VS2010.

I currently have Oracle 8i installed on my machine, and this is running TOAD v9.xxx, and also PL/SQL Developer, with VS2005.

All things are working fine.

I have actually been trying out VS2008 and VS2010 Express editions for quite sometime, but I have never ventured into the use of ODP.NET 11g.

Then about a month ago, I was able to get the installer for VS2010 Premium from our IT folks, and that is when my nightmares began.

Database Normalization eBook


Conflict of (OraHome) Interests

I didn't know it, but right on top of my working environment, I installed immediately, without trying out the earlier versions (ODAC ver9, ver10), ODTwithODAC112021. Well, I was able to run VS2010 codes using ODP.NET, and I was actually able to note that it is fast. I managed to create a simple code that lets me use stored procedures, which I immediately progressed to REF_CURSOR, as is the offered advantage when using ODP.NET, which is able to fetch multiple rows and multiple columns data set, which can be easily accessed as per normal (hasRows, etc., etc.).

But that is in exchange for a non-functioning TOAD, which is now unable to access some 'older' systems. 8i, 9i, etc. The error messages I got, which when I googled out to see if anything can be of help, did not help my situation. Yes, there are a ton of articles that should help me out, but to my situation, no help from the web was of help.

I got these: ORA-12541, TNS no listener, ORA-31xxx something, something, xxx is no longer accessible xxx, etc.

The thing is, when I run into these problems, that is when I found out that there is the known problem of versioning issues with Oracle clients, where the Home is switched from here to there with each installation, or that even when it didn't, a new conflict arises between the different Ora versions.

So I uninstalled ODTwithODAC112021, but that didn't restore my system to its working condition.

Thus began my half-month experimentation - mind you, I am not able to work during these times!


When one fails, will the others succeed?

I now tried installing Oracle 9i, which installed correctly, but TOAD can't perform login. Uninstalled.

I tried back ODTwithODAC112021, which resulted to the error: "OCI version 11.2.0.1.0 product is not supported", and when I googled this error msg, I found that TOAD has to be upgraded to version 10. Money?

Uninstalled ODTwithODAC112021, but still TOAD can't do login.

I now tried installing ODAC92070, but the setup was terminated without any error msg at all. It just quit on me!

I now tried to install back Oracle 8i client, but this one also throws out an error msg now, and can't be installed anymore.

Have I cracked my PC's nervous system? It is so confused now!

I attempted installation of Oracle 9i client, and while it installed correctly, TOAD still can't run. Uninstalled.

At this time, I looked at ODAC101040, installed it, and voila! TOAD runs. But wait a minute, VS2010 now throws out some error. Version problem? Unfortunately, I didn't capture the error message details. Somehow it has got to do with some service that isn't running. However, I did check that  OracleMTSRecoveryService is running from the Services view. Uninstalled.

As I am getting tired now, and running out of options, I planned to just make VS2010 run, declare that this is more the important thing, and call it a day. I installed ODTwithODAC112021, but at this time, even this one now throws out an error during installation. I can't proceed to install it!

I find that this is due to my home being selected as DEFAULT_HOME1, which is the very first home that was created with my old configuration when Oracle 8i was installed.

I changed that to OraClient11g_home1, and I was able to install ODTwithODAC112021, and then I was able to run VS2010 okay, but not TOAD (at least, some newer systems I can connect to, but not all, esp those running on old servers).

I went to sleep.


A new day

I thought that I would be okay with what I now have, but I was wrong. I can't rest until everything works the same, as when I started, and yet I wanted to work with the latest versions. I repeated the whole exercise the whole day, again, but still ended up with either the VS2010 working, and TOAD not working, or the other way around.

Did I mention it took me half a month doing this? I did. A couple of days. But it is good that my boss never asked me on what I have produced within those 2 weeks while attempting to make things work on ODT.NET and VS2010.


Revo Uninstaller to the rescue (or so I thought)

After so many days doing install and uninstall actions, my system seems to have been corrupted. I can't even unstall the 8i, 9i clients completely, and also the ODAC 9, 10, and 11 versions. They remain in the system even after I have performed uninstallation. What's worse, my system doesn't work as before.

I have Revo on my system, so I went to 'Forced uninstall' mode, and I was glad I did. I cleaned up my system, even to the last bit of these Ora creatures - completely!

And right after the last clean-up, I'm not able to access internet, or even our intranet applications! I must have uninstalled some fundamental services when I did the clean-up! I did!

My TCP/IP was deleted together with the items when I did the forced uninstall!

So not only do I have a system that doesn't run TOAD, or VS2010. I now have a system that can't do network connections! And in a company that relies on networks for applications to run, my system has become useless altogether now!


A help from our User Support Team.

I quickly went to our User Support group, and I told him what happened. You see, I have other PCs at work that I used to google possible fixes when TCP/IP is broke, and I did all those things. Yes, there is help all over the web, but unfortunately, none of these, though helpful, helped my situation.

So I went over to the 'nut-and-bolt' guys, and I was advised to see if I can do it by system restore. So I grab that flicker of hope, immediately proceeded to do a system restore, and my PC was back! Now I only have those duplicates to deal with.

I now cautiously performed the uninstallation of the Oracle products. (You have to get burned before you become cautious and careful sometimes...) I checked, and I found that there was an article somewhere in the OracleForums, about manually uninstalling Oracle 8i, 9i, etc. I followed that to the letter after doing the OUI uninstallation actions. I edited the registry, removed the folders, delete the files in the Temp\ folder, emptied my Recycle bin, with the reboot after the registry edit and after emptying the bin.

At this point, I now have a 'clean' system.


Instant Client, ODAC92070 and ODAC112021 (XCopy version) - the three muskeeters

I was getting curious if Oracle Instant Client works, for me, I should say, since many do vouch for it. So as part of my curiosity, and with my exhausted energy that I can only do little and quickly at that, I now tested Instant Client, especially that the procedure is fast and simple.

Copy zip file, unzip, rename, define Path, and copy over my TNSNAMES.ORA, SQLNET.ORA and LISTENER.ORA. I was careful to put these in the correct folder.

Did my TOAD 9.xxx work? You bet it did!

However, as I have said, some older systems I still am not able to connect to. We're not done yet...

I now installed ODAC92070, taking note of any error messages that might get thrown during installation. Well, there was none.

Does TOAD still work? You bet it does!

And now, I am able to access the old Oracle server. Last hurdle, VS2010.

With all hopes dashed with the failed attempts on ODTwithODAC112021, I switched to ODAC112021, which is basically the same as the former, but is just different in the manner of installation, I tried the XCOPY version.

Finally, the system variable TNS_ADMIN was set to the final folder/directory, and testing was done:

TOAD - it works!
VS2010 - it works!


Victory, at last!

Boy, I still got what I wanted, and although I didn't get it so easily, there is prize for the persistent and diligent.

Till then!

Related articles

• Learn to use visual studio - Sheldon
• Installing Oracle on OS X Snow Leopard
• Links 7/6/2011: Platform 11, New Wine
• ACG Technologies Database Mobility for iPad/iPhone: Run Your Business from Virtually Anywhere
• Free PHP Webinar: How to Increase Performance with Caching
• HP delivers applications appliance solutions that leverage converged infrastructure for virtualization, data management
• GNOME Shell and Ubuntu Unity both offer new looks
• Data Dude, Visual Studio 2010 SQL Server Database Projects
• AForge.NET Visual Studio 2010 Setup (Tutorial)
• Why Opera mini sometimes does not work on Blackberry OS 6?

• Oracle Instant Client with ODP.NET (jeremybranham.wordpress.com)

Oracleclient handling multiple row result set in C#

Image via CrunchBaseAfter so many days of searching and experimenting on how to use stored procedures in my applications, and specifically, to use Oracleclient, I was getting tired and frustrated. None of my searches in the web is returning the exact article that I need. Until about today.

It was a search, some kind of a last recourse, a button click with my dying enthusiasm... the last blast.

And it somehow paid off!



When I saw the article in this page, I knew that the connection string is defined differently.

I kept on doing search and review and testing.

I should mention that I saw some useful articles in MSDN, where I also learned that when handling result sets that are multiple rows and/or multiple columns, the ordinary query or stored procedure will not work. The use of REF CURSOR is now a mandate.

One such useful reference can be found here. This is where I patterned my stored procedure, made up of a stored procedure in a package.

Then finally, today, I could say that as a last recourse, I typed on "Data Reader on Oracle stored procedure in C#", "using Oracle client to fetch multiple rows", and to the likes of that search strings, and I found one coming from The Code Project, where I am subscribed to.

I found one, which, I am not able to understand immediately. But the connection string is once again stressed out to be different.

I tried a few more times, and then finally, with one last try before I would ditch my attempts forever, it worked!

That page from The Code Project team is here.

I needed the Oracleclient dll file (.NET Managed Provider for Oracle), and as directed by the Code Project article, i downloaded the installer from a Microsoft Download page. Well, find that page here also.


So why did I pursue this item?


Speed and efficiency. Not to mention maintainability.

Stored procedures are many times more efficient that inline queries. The comparison I did was to run an inline query and the stored procedure. The timing I put in front and behind is to measure the time the small code would take to complete.

One query. Short one. Simple one.

The inline query took about 1 min and 30 secs.
The stored procedure took about 11 secs to complete.

That is approximately a speed of 6x faster or slower!

And if you need to change anything in the query, you can do it outside without ever disturbing your application - if you are using stored procedures.

And as always said, stored procedures already IN THE Database system are already optimized by the machine, rather that when it is first submitted and run by the machine.


And with all that has been done and tested, I am happy to present my simple code, for my own reference, and for everybody else who would find this useful.

Till then!

-----


Points to note:

1. Inline query is the same as the stored procedure, modified accordingly.
2. The parameters (input, output) should have the same names as declared in the stored procedure/package.

-----

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Text;
using System.Windows.Forms;

namespace Get_Data_by_OracleClient
{
  public partial class Form1 : Form
  {
    public Form1()
    { InitializeComponent(); }

    private void Form1_Load(object sender, EventArgs e)
    {
      TBox1.Clear();
      TBox2.Clear();
      TBox3.Clear();
      TBox4.Clear();
    }

    private void Btn_Click(object sender, EventArgs e)
    {
      this.Get_Data_by_InlineQ(TBox1.Text, TBox4);
      this.Get_Data_by_StoredP(TBox1.Text, TBox4);
    }

    private void Get_Data_by_InlineQ(String thisITMSDevice, TextBox thisTBox)
    {
      thisTBox.Text = "";
      //
      StringBuilder myQuery = new StringBuilder();
      string dbProvider = "Provider=MSDAORA.1;";
      string dbUser = "User ID=myUserName;";
      string dbPwd = "Password=MyPassword;";
      string dbName = "myOracleDBName";
      string dbODS = dbProvider + dbUser + dbPwd + "Data Source=" + dbName;
      OleDbConnection dbConn = new OleDbConnection(dbODS);
      StringBuilder myResult = new StringBuilder();
      myResult.Append(DateTime.Now.ToLongTimeString()).AppendLine();
      //
      myQuery.Append(" type in your inline query here ");
      //
      OleDbCommand dbCmd = new OleDbCommand(myQuery.ToString(), dbConn);
      //
      try
      {
        dbConn.Open();
        OleDbDataReader dr = dbCmd.ExecuteReader();
        myResult.Append(dr.FieldCount.ToString()).AppendLine();
        while (dr.Read())
        { myResult.Append(dr[0].ToString()).AppendLine(); }
        dr.Close();
        dbConn.Close();
        thisTBox.Text += myResult.ToString();
        thisTBox.Text += DateTime.Now.ToLongTimeString() + "\n";
      }

      catch (Exception ex)
      { MessageBox.Show(ex.ToString()); }
    }

    private void Get_Data_by_StoredP(String thisITMSDevice, TextBox thisTBox)
    {
      thisTBox.Text = "";
      //
      string dbUser = "User ID=myUserName;";
      string dbPwd = "Password=MyPassword;";
      string dbName = "Data Source=myOracleDBName;";
      string dbODS = dbName + "Persist Security Info=True;" + dbUser + dbPwd + "Unicode=True;";
      StringBuilder myResult = new StringBuilder();
      myResult.Append(DateTime.Now.ToLongTimeString()).AppendLine();
      //
      OracleConnection dbConn = new OracleConnection(dbODS);
      OracleCommand dbCmd = new OracleCommand();
      dbCmd.Connection = dbConn;
      dbCmd.CommandText = "GetData_by_Ora.GetData_by_StoredP";
      dbCmd.CommandType = CommandType.StoredProcedure;
      //
      OracleParameter myITMSDev = dbCmd.Parameters.Add("InputVar", OracleType.VarChar, 32);
      myITMSDev.Direction = ParameterDirection.Input;
      OracleParameter myPrcdName = dbCmd.Parameters.Add("ResVar1", OracleType.Cursor);
      myPrcdName.Direction = ParameterDirection.Output;
      //
      myITMSDev.Value = thisITMSDevice.ToString().Trim().ToUpper();
      //
      dbConn.Open();
      OracleDataReader rdr = dbCmd.ExecuteReader();
      myResult.Append(rdr.FieldCount.ToString()).AppendLine();
      while (rdr.Read())
      { myResult.Append(rdr[0].ToString()).AppendLine(); }
      rdr.Close();
      dbConn.Close();
      thisTBox.Text += myResult.ToString();
      thisTBox.Text += DateTime.Now.ToLongTimeString() + "\n";
    }
  }
}

 

Related articles

• SQL SERVER 2008 - Why one should not prefix user stored procedures with sp_ (sqlexplore.wordpress.com)
• Save Web Form Contents On Authentication Timeout (andrewgreatorex.wordpress.com)
• Control Your Defaults (infobright.org)
• C #(sharp).net - Ado.net (mstechexplore.wordpress.com)
• Steps to Debug stored procedure of SQL server 2008 using Visual Studio 2010 (venkatb4me.wordpress.com)
• Getting Started with SubSonic (rahulgbhatia.wordpress.com)
• Random CSV Test Data Generator (craigwatson1962.wordpress.com)
• What Are Stored Procedures? (stockinv.wordpress.com)
• inserting & retrieving images from database using stored procedures (prashanthchindam.wordpress.com)
• Stored Procedures (venkatb4me.wordpress.com)
• Oracleclient handling multiple row result set in C# (mycompuquest.blogspot.com)
• Coding Horror: Reddit: Language vs. Platform (codinghorror.com)
• Searching in Stored Procedures (mavenlog.wordpress.com)