locked
Help with Oracle to SQL SSMA conversion error O2SS0083 RRS feed

  • Question

  • I am not very savvy with either Oracle or MS SQL although I am not afraid to learn.  I have installed and configured various aspects of SQL Server 2012 a couple times now.  I am just at the starting phases of planning and testing for a migration from Oracle 11g to SQL Server 2012.  In my lab environment I am trying to migrate one of our smallest databases (only 3 tables) and the SSMA report shows an error.  There were a few errors but I was able to find information on the others and resolve them. 

    The error as shown in the bottom pane of SSMA is as follows:

    O2SS0083: SQL Server Migration Assistant for Oracle Error message: Unresolved identifier(9) Estimated manual conversion time: 1.8 hr(s)

    In the upper right pane (which I gather is the converted SQL) the message (and highlighted section) is:

     /*
     *   SSMA error messages:
     *   O2SS0083: Identifier DBMS_REPCAT_INTERNAL_PACKAGE.CALL cannot be converted because it was not resolved.
     
     EXECUTE DBMS_REPCAT_INTERNAL_PACKAGE.CALL
     */

    In the upper left window (which I gather is the unconverted SQL code) the code which SSMA is indicating as problematic is:

    DBMS_REPCAT_INTERNAL_PACKAGE.CALL('TIMECARD', 'TBLSTAFFID', 'REP_DELETE', 8);

    The full SQL code for this section looks like this:

      begin
        DBMS_REPCAT_INTERNAL_PACKAGE.CALL(
          'TIMECARD','TBLSTAFFID','REP_DELETE',8);
        DBMS_REPCAT_INTERNAL_PACKAGE.RAW_ARG(column_changed$);
        DBMS_REPCAT_INTERNAL_PACKAGE.VARCHAR2_ARG("PHOTO1_o");
        DBMS_REPCAT_INTERNAL_PACKAGE.NUMBER_ARG("STAFFID2_o");
        DBMS_REPCAT_INTERNAL_PACKAGE.VARCHAR2_ARG("STAFFMINS3_o");
        DBMS_REPCAT_INTERNAL_PACKAGE.VARCHAR2_ARG("STAFFNAME4_o");
        DBMS_REPCAT_INTERNAL_PACKAGE.VARCHAR2_ARG("STATUS5_o");
        DBMS_REPCAT_INTERNAL_PACKAGE.VARCHAR2_ARG(site_name);
        DBMS_REPCAT_INTERNAL_PACKAGE.CHAR_ARG(propagation_flag);
      end rep_delete;

    This particular packaged procedure is named rep_delete and it looks like this section is running some sort of loop, in this case 8 times.  There are a number of packaged procedures but all of the ones that are showing this error have a similar section and the number (8 in this case) corresponds with the number of lines under the problematic line.  The packaged procedures without a section like this seem to agree with SSMA just fine.

    In case it matters there are actually two packaged procedures that appear to be named rep_delete but I do not think this is the issue since there are also other packaged procedures that do not have duplicate names with this exact same error.  However on that note I am a bit suprised to see that there appear to be any packaged procedures with duplicate names (see image at link) and certainly hope this will not create additional problems.

    http://imageshack.us/photo/my-images/11/ssmaerrorc.png/

    Any help would be appreciated.


    • Edited by MnM Show Thursday, April 19, 2012 4:39 PM
    Thursday, April 19, 2012 4:24 PM

Answers

  • Hi MnM Show,

    >> O2SS0083: SQL Server Migration Assistant for Oracle Error message: Unresolved identifier(9) Estimated manual conversion time: 1.8 hr(s)

    Regarding to the error message seems related to estimated conversion time issue. SSMA automates conversion of most statement. There are a few features and syntax which SSMA is unable to migrate. In such situation SSMA provides estimated manual conversion time.
    For more information, please refer to How SSMA Estimates Manual Conversion Time http://blogs.msdn.com/b/ssma/archive/2011/07/11/how-ssma-estimates-manual-conversion-time.aspx

    >> O2SS0083: Identifier DBMS_REPCAT_INTERNAL_PACKAGE.CALL cannot be converted because it was not resolved.

    Based on my research, this seems might be an Oracle internal package that supplies functionality to the dbms_repcat procedures and functions, as it's an internal package it's code is not visible to anyone outside of the Oracle developers who created it.
    For more information please refer to DBMS_REPCAT_INTERNAL_PACKAGE
     http://www.serkey.com/tag/dbms-repcat-internal-package/

    Additional according to the error is SSMA error, for further help please directly contact to the blog of SSMA team.

    Regards, Amber zhang

    • Marked as answer by MnM Show Tuesday, April 24, 2012 4:38 PM
    Friday, April 20, 2012 7:26 AM

All replies

  • Hi MnM Show,

    >> O2SS0083: SQL Server Migration Assistant for Oracle Error message: Unresolved identifier(9) Estimated manual conversion time: 1.8 hr(s)

    Regarding to the error message seems related to estimated conversion time issue. SSMA automates conversion of most statement. There are a few features and syntax which SSMA is unable to migrate. In such situation SSMA provides estimated manual conversion time.
    For more information, please refer to How SSMA Estimates Manual Conversion Time http://blogs.msdn.com/b/ssma/archive/2011/07/11/how-ssma-estimates-manual-conversion-time.aspx

    >> O2SS0083: Identifier DBMS_REPCAT_INTERNAL_PACKAGE.CALL cannot be converted because it was not resolved.

    Based on my research, this seems might be an Oracle internal package that supplies functionality to the dbms_repcat procedures and functions, as it's an internal package it's code is not visible to anyone outside of the Oracle developers who created it.
    For more information please refer to DBMS_REPCAT_INTERNAL_PACKAGE
     http://www.serkey.com/tag/dbms-repcat-internal-package/

    Additional according to the error is SSMA error, for further help please directly contact to the blog of SSMA team.

    Regards, Amber zhang

    • Marked as answer by MnM Show Tuesday, April 24, 2012 4:38 PM
    Friday, April 20, 2012 7:26 AM
  • Thanks Amber!  You put me on the right track with your answer.  These packages are related to the multi-master replication that our Oracle servers are currently doing.  Since we will be removing this replication prior to the migration to SQL Server these packages should go away when we remove that replication.  For testing purposes I think I will try the migration without these packages and see how it goes.
    Tuesday, April 24, 2012 4:48 PM