Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: 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,
    Kishore

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Thanks Pat. Can you give me an expample of such vendor so that I can try their evaluation version?
    Kishore

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Try searching for SwisSQL - under download.com.
    http://www.eweek.com/article2/0,3959,990163,00.asp for information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Mar 2004
    Posts
    84
    Thanks Satya, That is exactly what I am looking for. I will give it a try to see if it can convert my scripts.
    Kishore

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2004
    Posts
    84
    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.
    Kishore

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Mar 2004
    Posts
    84
    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;
    /
    --------------------------------------------------
    Kishore

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Mar 2004
    Posts
    84

    Unhappy

    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.
    Kishore

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Don't scare him/her with functions, Brett, I think he/she has a lot to catch up

  15. #15
    Join Date
    Mar 2004
    Posts
    84

    Talking Thanks a lot guys

    I will be bugging you guys often from now on.
    Kishore

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •