Re: Question for you Oracle Guru's -- conversion of SQL to Ora

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Tue, 21 Dec 2004 17:12:06 -0800
Message-ID: <41c8c8e6$1_3_at_127.0.0.1>


oai_at_bigfoot.com wrote:

> I have been tasked with converting a MS SQL 2000 db to Oracle 10g. I am
> the one that will be writing the updates to the table structures,
> taking existing sp's and moving them to pl/sql. My problem is I have
> not done this in a good number of years, converting sql to ora, and I
> frankly am stuck on how to deal with ms's use of identity columns in a
> table. I know RowId exists in Oracle but I have forgotten how to use
> it, i.e. do I have to maintain a sequence in pl/sql to use it?
>
> The question I am getting to is what is the best source of readily
> viewable information on the different datatypes in each system and how
> they equate to each other. Also, what do you use to ensure uniqueness
> in a table in ora such that ms sql2000 uses an identity column.
> Any help is greatly appreciated.

Based on the questions you've asked you might want to seriously consider giving this project to someone else. You can assign nor use rowd as rowid is a pseudocolumn: Always has been AFAIK.

To replace identity column you should use a sequence with the number being assigned either in the DML or by means of a BEFORE INSERT trigger.

As for the code I would suggest you rewrite from scratch. Oracle doesn't take kindly to code written where locks escalate, where multiversioning doesn't exist, and where people use temporary tables built on-the-fly.

My recommendation is that you purchase a copy of Tom Kyte's book "Expert one-on-one Oracle" and pay special attention to the first three chapters.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
   http://www.newsfeed.com       The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----
Received on Wed Dec 22 2004 - 02:12:06 CET

Original text of this message