7 Replies Latest reply: Jul 6, 2012 9:44 AM by Pnauduri-Oracle RSS

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

    user12240205
      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?
          Pnauduri-Oracle
          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
            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?
              Pnauduri-Oracle
              "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
                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?
                  Pnauduri-Oracle
                  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
                    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?
                      Pnauduri-Oracle
                      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