Re: From OpenIngres to Oracle

[David Donnelly <Dave@ISISBIO.COM>]

Hi Aaron,

First of all, be happy you don't have to go from Oracle to Ingres!

I have an extensive application that had to run both in Oracle and Ingres, but 
it evolved in the early 1990s.  I generally found Ingres (version 2.28f) more 
restrictive than Oracle, and of course there are the semantic differences to be 
addressed, but in general there weren't any SQR issues. There were some things 
that did not work correctly with Ingres, notably determining the correct type 
when functions were used in selects, but since you are going FROM Ingres these 
issues should have already been ironed out.

Here are some of my notes:

Since Oracle converts datatypes more automatically than Ingres, you may find 
many type conversions you no longer need, but they are fine to keep.

The date processing in Ingres is much cleaner than Oracle; basically if it 
looks like a date, Ingres will convert it from char to date type.  One odd 
thing: dates stored with times of midnight in Ingres are converted to character 
as if there were no time; Oracle will return a time of 00:00:00.

In Ingres, "where not exists" performs very poorly, but this is not true in 
Oracle.

In Ingres, there can be zero-length character strings. These are different from 
NULL. In Oracle, these would appear to be NULL. I'm not sure if this is true 
any more.

In Ingres, there is a maximum length of a line in a begin-select; I think it 
was 255, and SQR used to blow up if you exceeded it. I don't think this happens 
in Oracle.


Semantics:

char vs to_char
date vs to_date
date('now') or date('today') vs sysdate
lower vs lowercase
pad vs rpad
locate vs instr
+ vs ||
date_trunc vs date
INT4 (etc) vs floor
"as" in column aliasing vs none (doesn't affect SQR)
COPY from/to table not in Oracle
system catalog differences
updates with coordinated subqueries have vastly different syntax

Hardly anyone seems to use Ingres anymore; please feel free to email me if you 
have any questions.

Dave






At 12:56 PM 4/5/2001 +0200, you wrote:
>Hi,
>
>I need to make a list of issues for a site that plan to move from 
>CA-OpenIngres to Oracle.
>Do you have some tips for us ? (about SQR of course ;-)
>
>Thanks in advance.
>
>Regards,
>Arnon Oppenheimer
> -------------------------------------------------------------------
>SEMECH SOFTWARE MARKETING LTD.
>TEL : (972) - 3 - 5333144
>FAX : (972) - 3 - 5333132
>Email: arnono@semech.co.il
>-------------------------------------------------------------------


Dave Donnelly           <dave@isisbio.com>
ISIS BioComp            phone (909) 677-2446      fax (781) 207-5533