Tuesday, February 1, 2005

Oracle Time

Sorry it's been so long since an update. I have been working and coding hard. I was presented with the challenge of adding native ODP.NET (Oracle Data Provider) support to my current project. For those who are looking to get a start on Oracle here is what I did.


First, you of course need an Oracle database. Luckily for you and me, it is freely downloadable from Oracle. I guess they have so many paying customers that they aren't worried about locking up their software. That was refreshing. Head over to Oracles download page and get the most recent release. I was using 10g.

I would recommend doing the install on a box with a static IP address. I had a lot of troubles with the installation on my DHCP box. Once I made the IP static, no more exceptions were thrown. So the first trick is how to run the install. You have to run the setup.exe file from X:where ever you expanded the zipDisk1install. I tried the one directly off of Disk1 and got no joy. I accepted the defaults and had myself an Oracle database soon thereafter.

Connecting the the Oracle database is an interesting adventure. Make sure to install some client tools on your development box if it is different than you development database server. I downloaded the Oracle 10g Data Provider for .NET 10.1.0.3.01 including ODAC from Oracles web site and ran it's install. Once this is done, run the Net Configuration Assistant from the Oracle - OraClient10g_home1Configuration and Migration Tools folder. It should be in your start menu. Follow the directions and make sure you can run a successful connection test before going any further. This took me some time to iron out because the initial install was done when the server was DHCP and left behind some mess in my TNSNAMES.ORA file.

Populating the data was another challenge. Oracle doesn't seem to provide any sort of fancy schmancy admin tool like SQL Servers Enterprise Manager. They have an Enterprise Manager but it is web based (nice), semi-organized (nice) but not close to as GUI as MS-SQL EM. I decided I was going to use Microsoft DTS to copy the data from my existing MS SQL database to Oracle. Seems like a smart idea right, wrong. I guess Microsoft planned for this type of disloyalty and made some interesting "features" to the transformation to an Oracle source. The initial load failed on every table that had multiple GUIDs in it. This was ok with me, I decided I would just hand code the other tables using the iSQL plus interface. After the load, I decided to try a simple SELECT * from table query in the iSQL interface. No luck. Weird. The Enterprise Manager showed the tables as there but I couldn't select them. I finally tracked it down, DTS had added double-quotes around each table name so SELECT * FROM "TABLE" worked but SELECT * FROM TABLE didn't. Ugg. This of course didn't show up until I drilled all the way down in the table in the EM utility.

I decided that there had to be a better way. A Google search showed me the light, enter Oracle Migration Workbench. Now this baby handles the migration much better. I downloaded it and the Microsoft SQL Server 2000 plug-in and was able to get it up and running. With some basic trial and error, I got my tables imported. Of course, they were all under the wrong schema of SA instead of the one I wanted but I found out it I changed the owner to another user in MS SQL and then re-ran the import (after deleting the old tablespace), it came in correctly. The other caveat was changing datetime fields to use timestamp instead of Oracles date (otherwise you get a bunch of truncated millisecond errors).

Now for the real work, rewriting the data tier component to handle Oracle. This was actually a lot easier then I thought. It took me about 10 minutes (after 3 days to get the Oracle DB up). I had to add a reference to the ODP.NET (Oracle.DataAccess) component and then all I had to do was replace SQL with Oracle in all of my calls and blamo, it worked! (Try it, you can actually do a search and replace).

The only issue I have is that my multiple table/single SQL statement calls are failing but I think I can write a parser to handle this. (e.g. SQLString = "SELECT * FROM TABLE1; SELECT * FROM TABLE2")

All in all, this was a good experience, I am glad I didn't have a local Oracle guru to speed me through this, I learned a lot.

No comments:

Post a Comment