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

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
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)