SQL 2000 to Oracle 9i Migration Problems

ss_dash

Hi all,

I am using the Oracle Workbench for migrating my DB currently on Sql
Server 2000 to Oracle 9i. During the migration the following happens

SELECT @tBaseTable = tBaseTable, @tDBColumnName = tDBColumnName

FROM tblColumnMain WITH (NOLOCK) left join tblCustomField WITH
(NOLOCK) on aColumnID = nColumnID

WHERE aColumnID = @nPropertyID

gets converted to

SELECT tBaseTable, tDBColumnName

INTO

SPROC_PROPDATA.tBaseTable, SPROC_PROPDATA.tDBColumnName, FROM
sa.tblColumnMain, sa.tblCustomField

WHERE (aColumnID = nColumnID) AND (aColumnID =
SPROC_PROPDATA.nPropertyID_);

and gives the warning that complex outer joins maynot be reliably
converted

On the other hand the following code

select @keyid = a.keyID, @pubs=b.pubs from

ssd1 a left outer join ssd2 b on a.l_name = b.l_name

where b.l_name = @name

migrates perfectly to Oracle as

SELECT a.keyID, b.pubs

INTO SPROC_JOINTEST8.keyid, SPROC_JOINTEST8.pubs

FROM ssdash.ssd1 a, ssdash.ssd2 b

WHERE (a.l_name = b.l_name(+)) AND

(b.l_name = SPROC_JOINTEST8.name);

with the same warning ...

Could anyone enlighten me as to what exactly is the difference between
these 2 above cases.

Also the migration bench seems to be removing nolocks and such stuff all
around. Any workaround for this is also needed.

ssDash
--
Posted via http://dbforums.com

Jul 20 '05 #1

Daniel Morgan

ss_dash wrote:

Hi all,

I am using the Oracle Workbench for migrating my DB currently on Sql
Server 2000 to Oracle 9i. During the migration the following happens

SELECT @tBaseTable = tBaseTable, @tDBColumnName = tDBColumnName

FROM tblColumnMain WITH (NOLOCK) left join tblCustomField WITH
(NOLOCK) on aColumnID = nColumnID

WHERE aColumnID = @nPropertyID

gets converted to

SELECT tBaseTable, tDBColumnName

INTO

SPROC_PROPDATA.tBaseTable, SPROC_PROPDATA.tDBColumnName, FROM
sa.tblColumnMain, sa.tblCustomField

WHERE (aColumnID = nColumnID) AND (aColumnID =
SPROC_PROPDATA.nPropertyID_);

and gives the warning that complex outer joins maynot be reliably
converted

On the other hand the following code

select @keyid = a.keyID, @pubs=b.pubs from

ssd1 a left outer join ssd2 b on a.l_name = b.l_name

where b.l_name = @name

migrates perfectly to Oracle as

SELECT a.keyID, b.pubs

INTO SPROC_JOINTEST8.keyid, SPROC_JOINTEST8.pubs

FROM ssdash.ssd1 a, ssdash.ssd2 b

WHERE (a.l_name = b.l_name(+)) AND

(b.l_name = SPROC_JOINTEST8.name);

with the same warning ...

Could anyone enlighten me as to what exactly is the difference between
these 2 above cases.

Also the migration bench seems to be removing nolocks and such stuff all
around. Any workaround for this is also needed.

ssDash
--
Posted via http://dbforums.com

Can't help you with why other than to tell you that the migration tool
is intended to get you part-way there. There is no way it can handle
everything. The differences between the products are huge and range from
locking to the definition of a transaction. From trigger types to the
difference between autonumbering and sequences. And, of course, the fact
that temp tables are completely unnecessary in Oracle.

In short ... I wouldn't spend more than a few seconds looking this over
and just comment it out and convert it by hand.

A big help will be Tom Kyte's book "Expert one-on-one Oracle" by WROX
though WROX bought the farm so copies are hard to find. The first three
chapters deal extensively with the architecture differences.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #2