Re: Converting an Ingres database to Oracle

In article <3134DD21.1D4B_at_cho.ge.com>

           doug.ramirez_at_cho.ge.com "Douglas M. Ramirez" writes:

>I'd greatly appreciate any information with regards to tools,
>methodologies, timing, etc. in converting an Ingres database to Oracle.
> The tables and indexes are obviously easy to migrate. The thing that
>concerns me most is migrating Ingres database rules, events,
>integrities, and procedures (very big recursively processed
>procedures!).

As you say the tables and indexes are easy - especially now that Oracle have finally introduced a hash index after saying for years that it wasn't necessary.

Integrities you can do with constraints or stored procedures; events you can do using dbms pipes; recursive procedures you can do using stored procs again but rules you probably can't do. Two reasons for this:

The first is the "mutating row" problem. For some really, really dumb reason Oracle won't allow a row based trigger to update the row that caused the trigger to fire. Ingres, of course, allows this so there is a very good chance that your code will fall foul of this restriction.

Secondly Oracle will only allow one trigger of each type per table (I believe this restriction is due to be lifted soon - 7.3 perhaps) which means that if you have several rules that fire on one table then you have to merge the code into one all-encompassing procedure. As you can imagine, this makes code maintenance, debugging, testing much harder than it need be.

Triggers are easily the most braindead part of Oracle that I have so far come across. I strongly advise that you try and prototype a couple of tables that have several rules/procedures attached just to make sure that conversion is possible. If not then it doesn't preclude going to Oracle but you will have to allow time for a re-design, not just a re-code, of the system.

Finally (if this wasn't enough already) Oracle's procedural code makes use of exception handling to handle errors rather than Ingres' more procedural approach. Be careful that you put in place a mechanism for converting error handling code consistently.

Regards,

-- 
-------------------------------------------------------------------------------
Bruce Horrocks
Hampshire, England
bh_at_granby.demon.co.uk