October 8, 2012

Recently I was tasked with extracting some data from an Oracle 10gR2 server and converting it to SQL Server 2008. I could not get very much information about the Oracle server other than a sys password and the location of the nightly backup dump files. The server was serving two applications with very little vendor support – but they were working. The icing on the cake was that the the Oracle EM web app was not working – so I was also running a little blind. I didn’t want to break anything so I opted to leave the EM non-functional and I decided to import a recent dump into a fresh scratch Oracle instance. I would do my export to SQL server from there – zero risk of an oops.

The dumps were full system dumps and were tens of GB in size. I needed to know the names of the schemas being backed up so I could selectively restore the schemas to my new Oracle instance. In my messing around I accidentally loaded one of the dumps into a text editor (that was smart enough to deal with large files) and noticed it was mostly text. Keep Reading

December 9, 2011

The time has come for Country / State-Province lookup tables for Oracle! Building on my earlier post on Country / State Lookup Tables for SQL Server I’ve built a new script for Oracle. This script will create two tables, two sequences for the primary keys, and two triggers to setup the auto-numbering. Run the script and you should be good to go!

November 8, 2011

This week I encountered an irritating situation with Oracle while working on code in the RADE application logic. In a nutshell I am building dynamically created parameterized insert and update statements based on the RADE metadata and the values entered by the user. The first call InsertNewRecord works perfectly. The next call UpdateExistingRecord however was not. The parameterized SQL was being created. The parameters were being created and assigned to the DbCommand. The ExecuteNonQuery() call was executing without returning an error. Oracle just would not update. Even more frustrating – this “just worked” in SQL server.

What were the differences?

The basic logic for insert was this (parts omitted because you probably just don’t care):

Get table metadata

Loop through fields in table

for each field retrieve the value from the UI

Add field to parameterized SQL statement with placeholder

Create new parameter with appropriate name and value. Add parameter to collection

Loop through parameters in the collection and add to the DbCommand

Finally execute the parameterized SQL statement

As I mentioned this worked great. Fields were inserted and there was much rejoicing.

The logic for an update was similar but there was one big difference:

Get Table

Loop through fields in table

for each field retrieve the value from the UI

If the field is a key add the placeholder to the where condition, otherwise add the field name and value to the update fields part of the SQL

Create new parameter with appropriate name and value. Add parameter to collection

Loop through parameters in the collection and add to the DbCommand

Finally execute the parameterized SQL statement.

The branch in step 4 and the if statement ended up causing the problem.

The Problem

OracleCommand defaults to “bind by order” – making the order in which the parameters exist in the SQL statement match the order in which the parameters are added to the OracleCommand. This was happening during the insert because of the structure of an insert statement being so linear. However in the update statement I was building the SQL in a more dynamic way. I was maintaining a list field=value conditions and a separate where condition. In the ended up merging them :

So unless my key field( s) all lined up at the end of the table metadata definitions, appending that where condition at the end my parameter order got all out of whack in the DbCommand. So my where condition was actually being set to the wrong value – which could have resulted in the wrong records being updated. Nasty. Fortunately this can be resolved.

The Fix – BindByName=true

To correct this I had to set the Oracle specific BindByName property to true. (btw this being the default behavior is just silly. All the other big data providers default to bind by name and Oracle should too. That’s a rant for another day though.) My initial solution was to check if the command is an OracleCommand and if found do a little casting to set the BindByName property then recast it back to DbCommand before executing the query. Constructive feedback is always welcome!

January 31, 2011

Long ago I gave up trying to get both the 64 and 32 bit Oracle clients working on Vista x64. I have for now adopted the approach that if it talks to Oracle – it needs to be 32 bit. Even my development projects are set to x86 only to ensure only the 32 bit Oracle client is loaded on my development machine.

I needed to install AutoCAD Map 3D 2011 to debug some ObjectARX code I’ve been working on so I downloaded the 32 bit installer and fired it up only to be informed that my platform was unsupported and I would need to install the 64 bit version. grrrr.

Google had very little to say on the topic. More junk about hacking MSI files with Orca. I took a look at setup.ini and there were far too many references to x64 – I don’t have time for this…Finally I came across the Longbow Software Converter. The product claims to modify your setups to allow the 32 bit installers to work on x64 operating systems, 40$. I couldn’t find any reviews or testimonials so I figured I’d give it a shot…

So here is your first testimonial Longbow Software =)

This actually worked great. Bought the convert, installed it to a virtual machine. The interface is dead simple – paste or browse to the location of the AutoCAD installation files and press Convert. Keep in mind if you’re running from a DVD you’ll need to copy the contents of the DVD to your PC or a network share so the files can be edited. I pointed the converter to an install on a network share. It chugged away for about a minute (keeping in mind this was on an underpowered VM). Within a matter of minutes I had AutoCAD Map 3D 2011 installing on my Vista x64 box with no problems. Ran AutoCAD and it seems to fire up no problem.

Bear in mind – for AutoCAD to work with anything Oracle – you need to have the 32 bit Oracle client running on your machine.

March 5, 2010

RADE and FullCircle and builds usually have the ODP.NET version in the web.confg set by the installer, but development builds usually come from my machine and are configured for use with the Oracle 11 ODP.NET. If this is set incorrectly, you will get an error that looks a little something like this:

The IDbCommand and IDbConnection implementation in the assembly Oracle.DataAccess could not be found. Ensure that the assembly Oracle.DataAccess is located in the application directory or in the Global Assembly Cache. If the assembly is in the GAC, use <qualifyAssembly/> element in the application configuration file to specify the full name of the assembly.

To use an different version of the Oracle client, the web.config must be edited and the QualifyAssembly section must have its version property set appropriately. This will also apply for an app.config too.

October 23, 2008

So, finally I got fed up with working from a 32 bit XP virtual machine and spent a little more time on this issue. I’ve managed to get my Vista Ultimate a la x64 mostly working. Where I went wrong the last time (aside from trying to figure this out in the middle of the night in a shitty mood because I had spent so much time head banging with a wall) was using the 64 bit client.

The correct path to Oracle happiness in my case was the 11g client. It works great with my 10.2 server. The important bit in was to use the 32 bit install rather than the 64 bit install. One of my primary tools for accessing Oracle is Toad for Oracle 9.x. Quest Software states that Toad for Oracle 9.6 is the first version to really support the 11g client. However, it does NOT support the x64 client.

So the last time I tried to solve this, after a long day, and a fun night of head banging with a wall – when I tried the 11g client, I used x64 cbuild – and saw that Toad wouldn’t work, I threw a little hissyfit and went to bed. Long story short, installing both the 32bit 11g client and 11g ODAC/ODP seems to have done the trick. Now – there is one important little piece o’ information that you will need to know. Though I have not confirmed this, I suspect that x64 applications will not be able to use the driver.

That said, from a development point of view you will need to set IIS to run at 32 bit. On IIS 5.x/6.x this is a server wide setting from a dos window:

If you’re running Vista or Server 2008 with IIS7, you have things a little better. You can configure each application pool to run as 32 or 64 bit. For winform development, change the compile properties of the project to set the target platform to be x86.

October 11, 2008

I hate this error. Someone would give me a new Oracle dump file. I’d try to view the spatial data and boom – ORA-13226. If I was lucky, there was only a couple of tables in it. Chances are, there were hundreds of tables in it. Sometimes I’d need to add the reference to the USER_SDO_GEOM_METADATA view, sometimes I wouldn’t. Sometimes the index would already exist and just need to be rebuilt.

Regardless of the scenario, it would often involve a lot of SQL – or messing around to try and remember the SQL. Every time this would pop up I would think to myself, “I really should write an app…”. Well I finally did.

So, here is my announcement. The RADE Spatial Indexer is just about ready for beta. Initially this app does three things.

It will list tables and views with geometry columns that are not part of the USER_SDO_GEOM_META data view and help you add them.

It will list spatial tables without indexes and help you create them.

It will list spatial tables with indexes and help you re-index them.

No more exporting queries to text files and using a macro to edit them to build your SQL for rebuilding those indexes. I’ve tried to add some nice friendly helpers, such as a button that will suggest the spatial metadata settings for a table based on the existing meta data records. The ability to mass create or rebuild indexes is also been a fantastic helper.

I’m recruiting brave individuals for a small closed beta program that will begin in about two weeks. If you are interested in being a tester please e-mail indexer (at) landorIS.com and let me know. Remember, this is a beta – so I really do not suggest you run this on a production, or important server. That said, I have been using it in various states on my own ‘production’ development Oracle server.

As an incentive for helping me out. If you submit feedback (be it a bug report or feature request) – I will set you up with a free license once we release 1.0. I know you’re hooked and want to sign up. I know it. Just in case you need a little more, here is a few screen caps..

Look at how easy it could be to define metadata. Click the suggest button and it will iterate through the metadata settings for other spatial tables in the schema. Once you have one row defined, use the sync button to set all rows to the same values. Click process and its done.

Within seconds, have hundreds of spatial indexes being re-created…Creating new indexes is almost as easy.

July 11, 2008

Since moving to Vista x64 I’ve had a heck of a time with Oracle clients. The one thing I could not get working until tonight was ODP with Visual Studio / .NET. Finally I found a solution.

First, download and install Oracle 11g ODAC and Oracle Developer Tools for Visual Studio. (Link requires registration) This should get the 32bit stuff installed. I’m still using an Oracle 10g R2 server. You will likely need to grab a copy of the TNSnames.ora for your existing client folder and place it in the appropriate tree of the 11g product home.

This however is not enough to get .NET working with ODP. Go to the folder where you extracted the zip. We need to find the Oracle.DataAccess.dll. This can be found in the file named filegroup4.jar, in the stage\components\oracle.ntoledb.odp_net_2. Winrar will open .jar files if needed. Extract the Oracle.DataAscess.dll file.

For now, I’ve put a copy of this file in my projects lib folder. I then added a reference directly to this file from all projects that need ODP access.

Keep in mind – before you ship you may want to remove this reference and ensure that the .DLL file doesn’t get included in your build. This should get your Vista x64 box developing with ODP.