Convert SQL between Oracle and SQL Server

Hi, Is there a tool that anyone here is aware of which converts SQL, stored procedures and such stuff from Oracle compatibility to SQL Server compatibility? I have some Oracle based SQL that I wud like to convert into T-sql instead of re writing the whole T-SQL..
Thanks,This is one of those "holy grail" kinds of quests. I don't know of such a beast, although there are many vendors that are trying to sell products that get part of the way to solving this problem.

-PatP|||Thanks Pat. Can you give me an expample of such vendor so that I can try their evaluation version?|||Try searching for SwisSQL - under download.com.
http://www.eweek.com/article2/0,3959,990163,00.asp for information.|||I can't really give you any good suggestions for automated conversion between SQL dialects. While I've looked at a number of them in the past, I haven't looked in at least two years. There isn't anything that does what I consider a tolerable job for DML.

Now DDL (schema) conversion is another story. Assuming that the source database uses DRI (declarative referential integrity), then there are products that do a good job of moving your table structure from one database platform to another.

-PatP|||Thanks Satya, That is exactly what I am looking for. I will give it a try to see if it can convert my scripts.|||You fail to mention what versions...

Also DDL converts easier bexcuase it is waaaaaaaaaaaaay more straight forward...

Also the level of complexity of the DML could make a "translator" reach potentially incorrect results...

How do you plan to audit the conversion?

You will need to makes sure the results produced are identical...

Also, Oracle uses A LOT od cursors, because of it's architecture...

That would not be a good think for sql server

Why are you converting?|||I already have the DDL ready which I coverted and cleaned manually. I have some data integrity check and other migration scripts which are in Oracle that I need to make them compatible and equavalent to SQL Server 2000. Yes, we do have a lot of cursors in Oracle. How do we handle them then?
Thanks.|||Are they reference cursors?

Can you supply a sample?

What's the front end written in? Or is it all batch?

Do you have a small sample package?

Maybe you can attach one.|||This scripts verify the ingegrity of data in our database...
We have functions, procedures, PL/SQL that we cant to convert..
Here are some sample blocks..It is all a batch sql run against our DB which inturn writes all the errors/inconsistancies into an table called error_report.
----
create or replace function getlatestrelchg(itemid number) return number is
cursor csr is select a.change from rev a, change b where a.item = itemid
and (b.class = 6000 or b.class = 1450) and (b.statustype =3 or b.statustype=4)
and a.released = 1 and (b.delete_flag is null or b.delete_flag = 0) and a.change = b.id order by a.release_date desc;

relchgid number;
begin
open csr;
loop
fetch csr into relchgid;
exit;
end loop;
close csr;
return relchgid;

end;
/
--------
declare cursor nodecsr is select parentid from nodetable where parentid in
(select id from nodetable where objtype = 13 and id not in (11610, 16633, 16445)) group by parentid having count(*) != 4;
subclassId number;
Subclassdesc varchar(30);
begin
open nodecsr;
loop
fetch nodecsr into subclassId;
if (nodecsr%notfound) then
exit;
end if;
select description into Subclassdesc from nodetable where id = subclassId;
insert into error_report values ('NODETABLE', subclassId, 'Corrupted Subclass: ' || Subclassdesc);
commit;
end loop;
close nodecsr;
end;
/
-----------
rem This script ignores orphaned privilege masks (objtype=22)
rem
declare
cursor csr is select id, parentid, objtype, inherit from nodetable;
nodeID number;
parID number;
inheritID number;
errorCount number;
objectType number;
tmp number;
tmp1 number;
begin
errorCount := 0;
open csr;
loop
if errorCount > 30 then
insert into error_report values ('NODETABLE', 0, 'Too many errors');
exit;
end if;
fetch csr into nodeID, parID, objectType, inheritID;
if csr%notfound then
exit;
end if;
if (objectType !=22 and parID != 0) then
select count(1) into tmp from nodetable where id = parID;
if tmp = 0 then
errorCount := errorCount + 1;
insert into error_report values ('NODETABLE', nodeID, 'parent node does not exist');
end if;
end if;
if (objectType !=107 and objectType != 9 and inheritID != 0) then
select count(1) into tmp from nodetable where id = inheritID;
if (tmp=0) then
errorCount := errorCount + 1;
insert into error_report values ('NODETABLE', nodeID, 'inherit node does not exist');
end if;
end if;
-- check the comma in the attribute name
select count(1) into tmp1 from nodetable where description like '%,%'and id = nodeid and objtype = 1 and parentid !=12863;
if tmp1>0 then
errorCount := errorCount + 1;
insert into error_report values ('NODETABLE', nodeID, 'attribute name contains bad character - comma');
end if;
end loop;
close csr;
end;
/
----------------|||I think a couple of SQL Server DBA heads just exploded...

Damn now I gotta clean my screen...

How much code do you have?

You're in for a pretty good re-write...

Again, why the migration?|||I am new to the T-SQL area and given the responsibility of replicating whatever we have in Oracle as we started supporting SQL Server for our product.|||Well, your 1st function doesn't seem to make much sense, I mean, you've got a cursor, then returns a single values?

Anyway, this is how you would convert it...there are other ways to write it btw...

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getlatestrelchg]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getlatestrelchg]
GO

CREATE FUNCTION getlatestrelchg(@.emid int)
RETURNS int
AS
BEGIN
DECLARE @.relchgid int
SELECT @.relchgid = a.change
FROM rev a
INNER JOIN change b
ON a.item = itemid
WHERE ( b.class = 6000 or b.class = 1450)
AND ( b.statustype =3 or b.statustype=4)
AND a.released = 1
AND ( b.delete_flag is null or b.delete_flag = 0)
AND a.change = b.id
ORDER BY a.release_date desc

RETURN @.relchgid
END
GO|||Don't scare him/her with functions, Brett, I think he/she has a lot to catch up ;)|||I will be bugging you guys often from now on.|||I just converted the one they posted...

Hey, and you know what...a heavy dose of reality would be good here...I know both platforms...

It's going to be a major shift...

btw what's the product?|||Its a supply- chain product and there is a lot of dependency between the tables. So, the data integrity check is needed and heavily used.