We're currently running an application on SQL Server, relying on GUIDs for our Primary Keys. We're looking into switching to Oracle 10g, and so I'm doing some research into the switch and what kind of migration woes we're going to run into. So here's my question - can I switch out the database and adapter on the back end of a project without much difficulty, and what kind of issues am I looking at with using the Guids for idenifiers?

Thanks for any input you can provide!

ConfluentData wrote:

We're currently running an application on SQL Server, relying on GUIDs for our Primary Keys. We're looking into switching to Oracle 10g, and so I'm doing some research into the switch and what kind of migration woes we're going to run into. So here's my question - can I switch out the database and adapter on the back end of a project without much difficulty, and what kind of issues am I looking at with using the Guids for idenifiers? Thanks for any input you can provide!

The main issue you'll have is that Oracle doesn't support GUID's. Oracle also doesn't support a short byte array type, like binary, only blob style byte variables. You can mimic them with char fields in where you store the character representation of a guid, but even then you need to convert the data manually to GUID's in the entities as that's not available yet.

Oracle also doesn't have a bit field, so booleans have to be modelled onto char(1). If porting code isn't really a problem, these're the main problems you will run into.

I was more wondering how the mapper would handle this transition? Can I use GUIDs with Oracle and LLBLGen? Does it take these other changes into effect?

ConfluentData wrote:

I was more wondering how the mapper would handle this transition? Can I use GUIDs with Oracle and LLBLGen? Does it take these other changes into effect?

Currently there is no conversion logic possible for fields so data isn't converted behind the scenes. This is planned but not currently available (planned for april/may). So using GUIDs and oracle is not possible anyway, or you have to convert them to strings and use these, which won't make great PK's, and store the values in varchar2 fields, but this won't be automatic.

As well as the guid issue, theres loads of other stuff thats tricky to do in oracle. For example unicode support means that you have to setup the oracle database in a certain way if you want to "internationalize" (as MS say), and generally you'll have fun with the way oracle seems to make everything harder for you Oh, and the tools....don't get me started on the tools

Still, PL/SQL is powerful

Just a quicky on the tools: DbVisualizer can help. It can access to almost any db on hte planet and has powerful tools