Re: Conversion from SQL-Server to Oracle

On Sun, 29 Sep 2002 22:12:42 +0100, "Paul Brewer" <paul_at_paul.brewers.org.uk> wrote:

Oracle Workbench can convert some things for you,

Reasonable:
Table's
Constraints
Indexes
(Remark clustered indexes are sometimes converted to PK, this is not always correct).

Tricky
Views
(Simple views get converted correctly, more complex views get converted with a warning (there you should check the results). Some views can not be converted. Most problematic are outer joins and were constants are used in a join class).

Not ?
I do not thing Stored procedures can be converted at all. But there might be a way I do not know off.

General problems.
Oracle uses case significant 30 character identifiers limiting the the characterset to less characters than SQL-server. Oracle uses a different join strategie. Although from Oracle 9, it could be that the syntax is more similar.

Interfacing from application or application layers. A lot of SQL can be the same in Oracle and SQL-server. In most SQL-servers mixed mode with case not significant is used, here everything should be turned to UPPERCASE. (That is metadata, not the data).
For data most SQL-servers work also case not significant, here this could lead to different results specifically in the WHERE clause.

Concurrency,
SQL-server uses locking and isolation levels. Oracle uses snapshot isolation.
If READ-consistency was used in SQL-server then this should be sufficient in Oracle. Most repeatable read functionality can be reached by the Snapshot isolation. (Not completely sure there). If in SQL-server the SERIALIZABLE level was used, one has to check the application. If it depends on that the read data can not be changed, then you have to alter the application, because Oracle does not lock on reads. (And does not support Serializable completely)

Performance.
Both Oracle and SQL-server have their strenghts and their weaknesses. Primairy aim should be to get the application working correctly and knowing for sure it works correctly. Specific attention should go to concurrency even with testing software often concurrency issues do not appear until the software is used a lot on a large scale.

Depending on what features were used from one RDBMS the conversion can be from quite simple to a complete redesign.

ben brugman

>"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
>news:3D938351.E4630428_at_exesolutions.com...
>> Christoph Seidel wrote:
>>
>> > yes, this is the name :-)
>> >
>> > "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
>> > news:YUEk9.451742$_91.643468_at_rwcrnsc51.ops.asp.att.net...
>> > > I think you mean the migration work bench. You can download it from
>> > > otn.oracle.com
>>
>> That being said the migration workbench is just the very first step in a
>> process that will require you to modify your code. There is no other way
>> unless data corruption, poor performance, a lack of scalability, and
>> minimal security are acceptable.
>>
>> The architecture differences between Oracle and SQL Server are vast. While
>> the code produced by Migration Workbench is compilable code. It is only a
>> starting point.
>>
>> Among the critical differences are things in the Oracle word referred to
>> by the following phrases:
>>
>> 1. Multiversioning
>> 2. Readers don't block writers
>> 3. Writers don't block readers
>> 4. Unlimited locks
>> 5. Bind variables
>>
>> If you don't understand these concepts and modify your code accordingly it
>> will suffer greatly. I would suggest to you a small investment in Tom
>> Kyte's book "Expert one-on-one Oracle" and especially the first three
>> chapters.
>>
>> Daniel Morgan
>>
>Agreed. If this is a serious project, I'd recommend re-engineering; not
>migration.
>
>Paul
>
>
>

Ben Brugman Received on Tue Oct 01 2002 - 11:10:19 CDT