This discussion is archived
7 Replies Latest reply: Jul 6, 2012 7:44 AM by 84959 RSS

How to convert MS SQL Server 2008 Stored Procedures to Oracle 11g SPs?

user12240205 Explorer
Currently Being Moderated
We have a MS SQL Server 2008 application. We want to migrate it to Oracle 11g.

What is the Oracle recommended method for
(1.) Data migration (Tables, triggers, sequences, views etc.)
(2.) Stored Procedure migration.

What is the recommended tool or method pls??

We have 100s of MS Sql Server stored procedures. So, need a tool or method. We can't re-write all of them from scratch.
  • 1. Re: How to convert MS SQL Server 2008 Stored Procedures to Oracle 11g SPs?
    84959 Explorer
    Currently Being Moderated
    Use SQL Developer. It can migrate data, stored procedures,triggers, views, tables,indexes etc. Changes to the application will also be required so don't forget that.

    Regards

    Prakash
  • 2. Re: How to convert MS SQL Server 2008 Stored Procedures to Oracle 11g SPs?
    user12240205 Explorer
    Currently Being Moderated
    pnauduri wrote:
    Use SQL Developer. It can migrate data, stored procedures,triggers, views, tables,indexes etc. Changes to the application will also be required so don't forget that.

    Regards

    Prakash
    Thanks for the reply.

    Also, what u mean by "changes to application"???
  • 3. Re: How to convert MS SQL Server 2008 Stored Procedures to Oracle 11g SPs?
    84959 Explorer
    Currently Being Moderated
    "Changes to Application" can mean anything like:

    1. Changes to the database drivers, connection strings and other parameters.
    2. Porting of SQL Server specific SQL statements to Oracle compatible SQL statements.
    3. Handling of result sets
    and so on..

    You can get a thorough understanding about application+database migration in our book titled 'Migrating to the Cloud' : http://www.amazon.com/Migrating-Cloud-Oracle-Client-Modernization/dp/1597496472

    There is a general perception that database platforms can be changed very easily but it is important to understand the impact of DB on everything else that is built around it.

    Regards

    Prakash
  • 4. Re: How to convert MS SQL Server 2008 Stored Procedures to Oracle 11g SPs?
    user12240205 Explorer
    Currently Being Moderated
    Here is what I did to move our SQL Server 2008 to Oracle 11g.

    (1.) I read this document by Oracle. The only doc there is on migration:
    http://st-curriculum.oracle.com/obe/db/hol08/sqldev_migration/mssqlserver/migrate_microsoft_sqlserver_otn.htm

    Migrating a Microsoft SQL Server Database to Oracle Database 11g

    -- Problem with this method is that you need SQL Developer 2.1 (We used 2.1.1.64) for this. Latest versions (3.1) does not have the Migration > Third Party Database Offline Capture > Load Database Capture Script Output. This is weird, and this Oracle documentation is very poor because it does tell you the real SQLD version.

    -- So, we download SQLDev 2.1 and I try to do this (offline method). This works with the supplied test data. But, since we have to create the offline scripts this is long method.

    -- Then we used the "To view the steps for the online method, click here." option (i.e. http://st-curriculum.oracle.com/obe/db/hol08/sqldev_migration/mssqlserver/viewlets/sqlserver_online.swf)
    This method uses a live connection to a SQL Server DB to do the whole migration process online. Problem with this method is, we get stuck at stuck at Step 7. Capture the SQL Server DB. When we select this option in our SQL Develer 2.1, nothing happens. I think this is because it supports only SQL Server 2005 and does not support 2008. I am not sure. I stop this method here. Anybody can point to why it would be great?

    (3.) I use SQL Developer 3.1.07 and create a connection to the SQL Server DB.
    I then right-click the connection and click Migrate to Oracle... and use the Migration Wizard. It is also not clear and straightforward. It creates a script file and does not do it on-line. Why is this? You have to run the script and then load the data separately.

    (4.) After migrating the Oracle DB, I found that some procedures are invalid. On inspecting I get this this:

    This is the SQL Server Original:
    -- ========================================================
    -- Author          :     xxxxxxx xxx
    -- Create date     :     08 June 2011
    -- Description     :     Get Agent Payment By BatchNo
    -- ========================================================
    
    CREATE PROCEDURE [dbo].[AgentPaymentConfirmation_GetBy_BatchNo]
    (
    @BatchNo int
    )
    AS
    
    SET NOCOUNT ON;
    
         SELECT     
            [Id]
          ,AgentId
          ,[BatchNo]
          ,PaymentMode 
          ,Amount
          ,Data
          ,ExternalStatus
          ,[TimeStamp]
                   
         FROM AgentPayment
         WHERE BatchNo = @BatchNo
         ORDER BY Id
    Migrated Procedure in 11g:
    PROCEDURE AGENTPAYMENTCONFIRMATION_GET_1
    -- ========================================================
    -- Author       :   xxxxxxx xxx
    -- Create date  :   08 June 2011
    -- Description  :   Get Agent Payment By BatchNo
    -- ========================================================
    
    (
      v_BatchNo IN NUMBER DEFAULT NULL ,
      cv_1 IN OUT SYS_REFCURSOR
    )
    AS
    
    BEGIN
    
       OPEN cv_1 FOR
          SELECT Id ,
                 AgentId ,
                 BatchNo ,
                 PaymentMode ,
                 Amount ,
                 Data ,
                 ExternalStatus ,
                 TIMESTAMP
            FROM AgentPayment
           WHERE BatchNo = v_BatchNo
            ORDER BY Id;
    END;
    ____________________
    PL/SQL: ORA-00904: "DATA": invalid identifier
    PL/SQL: SQL Statement ignored
    My problem is, AgentPayment table does NOT have a DATA column both in the SQL Server and Oracle table.
    **** SCRIPT STARTED Session: DBO_ETA@orcl11gr1chan(1) 4-Jul-2012 13:28:31 ****
    DESC AGENTPAYMENT
    ==>
    Name                           Data Type                     
    ------------------------------ ------------------------------
    ID                             NUMBER(19,0)          NOT NULL
    ROWID_                         CHAR(36 CHAR)         NOT NULL
    AGENTID                        NUMBER(10,0)          NOT NULL
    BATCHNO                        VARCHAR2(20 CHAR)     NOT NULL
    PAYMENTMODE                    NUMBER(10,0)          NOT NULL
    AMOUNT                         NUMBER(19,4)          NOT NULL
    PAYMENTDATE                    DATE                  NOT NULL
    CARDTYPE                       VARCHAR2(20 CHAR)
    MERCHANTID                     VARCHAR2(50 CHAR)
    TRANSACTIONID                  VARCHAR2(50 CHAR)     NOT NULL
    PAYMENTDOCUMENT                BLOB(4000)
    PAYMENTDONEAT                  NUMBER(10,0)          NOT NULL
    EXTERNALSTATUS                 NUMBER(10,0)          NOT NULL
    DATECREATED                    DATE                  NOT NULL
    CREATEDBY                      CHAR(36 CHAR)         NOT NULL
    DATEMODIFIED                   DATE
    MODIFIEDBY                     CHAR(36 CHAR)
    TIMESTAMP                      RAW                   NOT NULL
    SQL*Plus statement executed
    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    **** SCRIPT ENDED 4-Jul-2012 13:28:32 ****
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    Also,
         DECLARE @QueryString varchar(MAX)
    migrates to Oracle as
     v_QueryString VARCHAR2; 
    How to solve these please???

    Edited by: user12240205 on Jul 5, 2012 8:33 PM
  • 5. Re: How to convert MS SQL Server 2008 Stored Procedures to Oracle 11g SPs?
    84959 Explorer
    Currently Being Moderated
    Hello

    Let me clarify some of the issues you have raised. But I will advice you to get some professional help with migration because:

    1. You need to understand the architectural differences between SQL Server and Oracle. They are different databases.
    2. Understand the datatype mappings.
    3. Understand the complete migration process. Just using the OTN article as a migration guide will not be of much help because it just demonstrates how to use SQL Developer for this task.
    4. Understand that 100% successful conversion to Oracle is not possible with any tools. There will always be shortcomings, functionality gaps, bugs etc. So there will be some amount of manual effort involved.
    5. There are lot of other tasks involved in the migration that cannot be accomplished with SQL Developer.

    Now getting to your questions:

    1. In 3.1, there is migration option for 'offline database capture'. It is under the tools option. So in 3.1 we don't have a separate tab called 'Migration'.
    2. Use 3.1 as it is much better than 2.1 in terms of capabilities.
    3. Migration Wizard is the simplest way to migrate. Where are you getting confused?.
    4. "Data" column seems to be in SQL Server stored procedure. If it is not there in the underlying table, why is it getting used in the SQL Statement in the procedure then?. I think we may have a 'reserved word' issue with the column name 'Data'. Will have to check that.
    5. VARCHAR(@max) should map into VARCHAR2(4000) in Oracle. It may be a bug in SQL Developer. So during the data type mapping staging in migration wizard you can change the default mappings.

    Regards

    Prakash
  • 6. Re: How to convert MS SQL Server 2008 Stored Procedures to Oracle 11g SPs?
    user12240205 Explorer
    Currently Being Moderated
    Another issue with the migration is that some tables, columns are missing. These tables and columns are not present in the SQL Server as well. It would be really good if we can identify these things by compiling the procedures in SQL server itself, but SQL developer does not have this facility.

    Also, The Migration tools will indicate where you have to modify with the 'TODO' comment in the procedure or functions. These have to be modified. In order to find out procedures and functions specified by migration tool to be modified after migration use this query:

    SELECT distinct name FROM USER_SOURCE WHERE upper(text) LIKE '%TODO%' order by 1;
  • 7. Re: How to convert MS SQL Server 2008 Stored Procedures to Oracle 11g SPs?
    84959 Explorer
    Currently Being Moderated
    First of all you need to identify a clean, fully functional SQL Server database to migrate. Make sure all dependent databases are also migrated. If you try to migrate a database that is not fully functional then you will need to put effort to identify issues in SQL Server first.

    Regards

    Prakash

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points