1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Server - Oracle translation

Discussion in 'Server Administration and Options' started by Al_Alter, Feb 21, 2011.

  1. Al_Alter

    Al_Alter Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi all!
    There is a task to develop and keep for a vile two databases. First in SQL server and second in Oracle. Original DB in SQL server. The “SQL developer” helps a lot. But it failed to translate functions with Table variables. Table is needed to temporary keep Array of substrings of Selection. Substring params (beg, length) are common for that Selection.
    Please advise me the best way to manage this task.
    As I wrote this I realized it could be done by just one SQL of rather complicated syntax. If you have an example at hand please post me.
    But those tables are also intended to use for keeping values that are missing in Selection. What is missed is determined by some complicated logic. So it can’t be avoided using something like tables. What would be the best choice to satisfy both SQL server and Oracle?

    Thanks in advance
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to provide more information. Post some sample SQL Server code you want translated so we can see the complexity of the task.
     
  3. Al_Alter

    Al_Alter Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    The function returns allowed values for user to choose and insert.

    ALTER FUNCTION [dbo].[MakeArray] (@charss Tinyint, @CorePartLen Tinyint, @CorePart char(18))
    RETURNS @ExcetpQ TABLE ([AllVars] [char] (18))
    AS
    BEGIN
    declare @Temp table (AllVars char(18))
    declare @TempComp table (AllVars char(18))
    declare @Limiter int, @CurInt int, @FullLenLeft int
    declare @CurChar char(18), @NulChar char(18), @FulChar char(18)
    declare @CorePartTrimm char(18), @FullFather char(18), @FullOncle char(18)
    set @FullLenLeft = @CorePartLen + @charss -- Width Up To Level Sect + Level Width
    set @NulChar = REPLICATE( '0', @charss )
    set @Limiter = POWER(10, @charss)
    SET @CurInt = 0
    WHILE (@CurInt < @Limiter) --creating list of all possible combinations
    BEGIN
    SELECT @CurChar = CAST(@CurInt As VARCHAR(18))
    SELECT @FulChar = right(@NulChar + @CurChar, @charss)
    insert into @Temp values(@FulChar)
    SET @CurInt = @CurInt + 1
    END
    if (@CorePartLen = 0) -- creating list of all taken (used) combinations
    BEGIN
    INSERT @TempComp SELECT DISTINCT left(CleanCode, @charss) FROM CleanTbl
    END
    else
    BEGIN
    exec GetFullFathers @CorePartLen, @CorePart, @FullFather OUTPUT, @FullOncle OUTPUT
    INSERT @TempComp SELECT DISTINCT right(left(CleanCode, @FullLenLeft), @charss) AS AllVars FROM CleanTbl
    WHERE ((CleanCode > @FullFather) AND (CleanCode <= @FullOncle))
    END
    -- making list of allowed values
    INSERT @ExcetpQ (AllVars) (SELECT AllVars from @Temp except select AllVars from @TempComp)
    RETURN
    END

    Actually I can store “List of all possible combinations”. It seems to me preferably in charlist type variable. This list isn’t frequently changed. Only when corresponding field is created or its width is changed.

    With best regards, Alex.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Have you tried using the Oracle Migration Workbench? It is designed for such tasks. You may also want to investigate using ERwin presuming your company has a license for that product or is willing to obtain one.