4 Replies Latest reply on Feb 29, 2012 4:52 PM by orcldeveloper-Oracle

    sql server stored procs migration to oracle

      Can't I migrate only the stored procedures alone of sql server db to Oracle using sql developer? How to load the .ocp files with the stored procs I have?
        • 1. Re: sql server stored procs migration to oracle
          You can generate the scripts needed for all the objects in a SQL*Server database and then just extract the code for the stored procedure creation.
          Go through the database migration steps while choosing the 'Online' option up to step 7 of 9 - Target Database - then choose the finish option.
          This will give you the the captured and converted models in the project.
          Then click on the converted model and choose 'Generate Target' which will give you the code from which you can extract the stored procedure code.

          If you already have a generated 'OCP' file then in Stpe 4 of the migration 'Source Database' choose the 'Offline' option and enter the OCP file name, then follow the steps again up to Step 7 to create the models, then you can again generate the scripts from the models.

          You do not need to go as far as choosing any of the 'Move Data' options if you just want the code generated from SQL*Server.


          Edited by: mkirtley on Feb 24, 2012 10:29 AM
          • 2. Re: sql server stored procs migration to oracle
            Hi Mike,

            For capturing the sql server stored procs...I have a question.

            I have handy stroed procs of sql server in text files. Using sql developer I generate the necessary capture file(.bat, .ocp,etc) and now I want to populate the .ocp file with the stored procs I have in hand.

            So, that with populated ocp I can go ahead with the load and conversion steps.

            Please let me know how can I populate the .ocp file for the stored procs I have in hand.
            • 3. Re: sql server stored procs migration to oracle
              I don't know how to manually update the generated captured files (bat, ocp) with other information or even if it is possible, as the bat files extract data from the SQL*Server data dictionary information to populate the files for the load. If the stored procedures have already been created in the SQL*Server database then they will added to the generated OCP information. If you want to manually change the files then you'll have to work it out for yourself, I'm afraid.
              If they are not in the SQL*Server database and exist only as the create statements in text files then you have 2 options, as far as I can see -

              - either run the scripts to create the stored procedures in the database before running the migration
              - use the translation scratch editor and put the procedure create SQL into that to convert to Oracle format -
              Tools > Migration >Translation Scratch Editor

              You need to be aware that translation of stored procedures isn't always automatically 100% completed and manual intervention is sometimes needed, depending on the complexity of the procedure and how it is written.

              • 4. Re: sql server stored procs migration to oracle
                You can also create a new database in Sybase and create all the stored procedures in that database. Then use SQL Developer to capture/convert that database only. But if you just have stored procedures in the database and no other dependent objects then you will see almost all stored projects as Invalid after conversion due to dependency issues.

                In SQL Developer, after you connect to the SQL Server database you can expand the object navigator and right click on a procedure and choose 'migrate to Oracle' also. It will work in SQL developer 3.1. This is another method to the use in addition to translation scratch editor that others have pointed out earlier.



                [*Migrating to the Cloud - Client/Server migrations to Oracle*|http://www.syngress.com/information-security-and-system-administrators/Migrating-to-the-Cloud/]