Re: Conversion from SQL-Server to Oracle

Swendel wrote:

> All of you, thank you very much for your contributions.
> I installed Oracle Migration Workbench (Oracle 9 version) and migrated a SQL
> Server 2000 database to Oracle 8.1.7.
> I realize and already expected to have to do some extra work to tailor the
> code for Oracle, but for my application this tool worked well enough as a
> start.
>
> Thanks again, cheers, Sander Wendel.
>
> "Ben Brugman" <benbrugman_at_onbekend.nl> wrote in message
> news:3d99c4ef.16147609_at_news.nl.uu.net...
> > 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

Before you begin modifying your code ... make sure you understand the huge architecture differences ... the basic one's which I can encapsulate in the following statements:

1. Multiversioning
2. Reads don't block writes
3. Writes don't block reads
4. Bind variables
5. Infinite row level locks

If you are not intimately familiar with these concepts and their implications get a copy of Tom Kyte's book "Expert one-on-one Oracle" and pay special attention to the first three chapters.

Daniel Morgan Received on Wed Oct 02 2002 - 10:01:41 CDT