1 Reply Latest reply: Jan 30, 2013 5:26 PM by Gary Graham-Oracle RSS

    Check Stored Procedures after Migration from MS SQL Server 2008 to Oracle11

    user480768
      I successfully migrated my application database (azteca) from MS SQL Server 2008 to Oracle 11g R2. After migration, I found there are few stored procedures are not valid. How do I check these invalid stored procedures and find what is wrong with them by using SQL Developer? Thanks for your help.

      Kevin
        • 1. Re: Check Stored Procedures after Migration from MS SQL Server 2008 to Oracle11
          Gary Graham-Oracle
          Hi Kevin,

          You posted quite a bit today, so perhaps you have already worked this out. If not...

          1. View -> Reports -> Data Dictionary Reports -> All Objects -> Invalid Objects [for a specific schema name]
          2. Next, for each invalid stored procedure listed in (1)...
          a. Open in the code editor from the Connections navigator tree
          b. Click on the Compile icon (two gears meshed together) in code editor tool bar.
          c. Look in the Compiler log pane for errors.
          d. Correct the errors
          Of course, success in addressing any errors depends on your skill level dealing with Oracle PL/SQL.

          Also, it may be helpful to read over section *3.2 Stored Procedures* in the supplementary migration guide:
          http://docs.oracle.com/cd/E35137_01/doc.32/e18462/trig_stored_proc.htm#CHDEIGBC

          Regards,
          Gary
          SQL Developer Team