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!

help me please to migrate from MS SQL to Oracle

Discussion in 'SQL PL/SQL' started by ecivgamer, Dec 17, 2013.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,

    we are in process of migration from MS SQL to Oracle.
    Please help me to perform the same in Oracle:

    Code (Text):

    select number+1 as rowid,number*6+1 as rowbeg,number*6+6 as rowend
    from master.dbo.spt_values
    where type='P'
     
    Our Oracle version:

    Code (Text):

    select * from v$version
     
    BANNER                                                                        
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production  
    PL/SQL Release 11.2.0.2.0 - Production                                        
    CORE 11.2.0.2.0 Production                                                    
    TNS for 64-bit Windows: Version 11.2.0.2.0 - Production                        
    NLSRTL Version 11.2.0.2.0 - Production                                        
     
    5 rows selected.
     
    Thanks for your attention.
     
  2. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Here is part of output:

    1 1 6
    2 7 12
    3 13 18
    4 19 24
    5 25 30
    6 31 36
    7 37 42
    8 43 48
    9 49 54
    10 55 60
    11 61 66
    12 67 72
    13 73 78

    ...

    and much more...
     
  3. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    solution 1:

    SELECT tab.RN +1 AS "ROWID",
    tab.RN *6+1 AS ROWBEG,
    tab.RN*6+6 AS ROWEND
    from (
    SELECT (rownum -1) rn
    FROM DUAL
    connect by rownum < 15
    ) tab;


    solution 2:

    select 1 as rnum, 1 as rowbeg, 6 as rowend from dual
    union all
    select rownum+1 as rnum,rownum*6+1 as rowbeg,rownum*6+6 as rowend
    from all_objects
     
  4. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    solution 1:

    SELECT tab.RN +1 AS "ROWID",
    tab.RN *6+1 AS ROWBEG,
    tab.RN*6+6 AS ROWEND
    from (
    SELECT (rownum -1) rn
    FROM DUAL
    connect by rownum < 15
    ) tab;


    solution 2:

    select 1 as rnum, 1 as rowbeg, 6 as rowend from dual
    union all
    select rownum+1 as rnum,rownum*6+1 as rowbeg,rownum*6+6 as rowend
    from all_objects
     
  5. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    solution 1:

    SELECT tab.RN +1 AS "ROWID",
    tab.RN *6+1 AS ROWBEG,
    tab.RN*6+6 AS ROWEND
    from (
    SELECT (rownum -1) rn
    FROM DUAL
    connect by rownum < 15
    ) tab;


    solution 2:

    select 1 as rnum, 1 as rowbeg, 6 as rowend from dual
    union all
    select rownum+1 as rnum,rownum*6+1 as rowbeg,rownum*6+6 as rowend
    from all_objects