locked
Error while converting schema from oracle to SQL server RRS feed

  • Question

  • Hello,

    I am getting following error while converting schema from oracle to SQL server using SSMA.

    I get Errors 1-3 while migrating procedures and error 4 while migrating a table.

    1- O2SS0050: Conversion of identifier 'SYSDATE' is not supported.
    2- O2SS0050: Conversion of identifier 'to_date(VARCHAR2, CHAR)' is not supported.

    3- O2SS0050: Conversion of identifier 'regexp_replace(VARCHAR2, CHAR)' is not supported.

    4- O2SS0486: <Primary key name> constraint is disabled in Oracle and cannot be converted because SQL Server does not support disabling of primary or unique constraint.

    Please suggest.

    Thanks.




    Monday, March 2, 2015 1:27 PM

Answers

All replies

  • Hello,

    For #2 and #3 try changing char by nchar.


    About  #4, SSMA cannot handle primary key and unique constraints that have been disabled. You may want to enable them or remove them.


    About the sysdate, I don’t know what could be because is supposed to convert it to sydatetime() in SQL Server as shown on the following article.

    http://blogs.msdn.com/b/ssma/archive/2011/05/12/migrating-oracle-to-sql-server-using-ssma-emulating-oracle-package-variables.aspx


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com



    Monday, March 2, 2015 2:02 PM
  • Hi Alberto,

    Thanks for the suggestions.

    Regarding error 3 and 3 can you please tell me where I need to make this change?

    Do I need to change it in local variable type mapping in SSMA?

    Here is some more information regarding errors 1-3. I have pasted below the source (oracle) and target(sql) codes from SSMA  where I am getting error.

    Error 1 -  O2SS0050: Conversion of identifier 'SYSDATE' is not supported.
    ---------
    source (oracle):
    ----------------
    sStartDate varchar2(25);
    dStartDate date;
    sStartDate:=substr(sDataRow,0,instr(sDataRow,sSep)-1);
    dStartDate:= to_date(sStartDate,'MON-YYYY');
    sStartYear:=TO_CHAR(dStartDate,'YYYY');
    -----------------------
    target (SQL):
    -----------------
    @sStartDate varchar(25), 
    @dStartDate datetime2(0),
     SET @sStartDate = substring(@sDataRow, 0, sysdb.ssma_oracle.instr2_varchar(@sDataRow, @sSep) - 1)

             /* 
             *   SSMA error messages:
             *   O2SS0050: Conversion of identifier 'to_date(VARCHAR2, CHAR)' is not supported.

             SET @dStartDate = (NULL)
             */
    SET @sStartYear = CONVERT(varchar(4), @dStartDate, 102)
    --------------------------------------------------------------------------------------------------
    Error 2 - O2SS0050: Conversion of identifier 'to_date(VARCHAR2, CHAR)' is not supported.
    --------
    source (oracle):
    ----------------
    dDate DATE;

    BEGIN
    SELECT SYSDATE INTO dDate FROM dual;

    -----------------------

    target (SQL):
    -----------------
     @dDate datetime2(0)

             BEGIN TRY

                /* 
                *   SSMA error messages:
                *   O2SS0050: Conversion of identifier 'SYSDATE' is not supported.

                SELECT @dDate = (NULL)
                */
    ----------------------------------------------------------------------------------------
    Error 3- O2SS0050: Conversion of identifier 'regexp_replace(VARCHAR2, CHAR)' is not supported.
    --------
    source (oracle):
    ------------------
    sDatarow varchar2(4000);
            WHILE nCount2>55
            LOOP
                sDataRow:=substr(sDataRow,instr(sDataRow,',')+1);
                nCount2:= length(regexp_replace(sDataRow,'[^,]'));
            END LOOP;

    ----------------

    target (SQL):
    -------------

    @sDatarow varchar(4000), 
    BEGIN

                   SET @sDataRow = sysdb.ssma_oracle.substr2_varchar(@sDataRow, sysdb.ssma_oracle.instr2_varchar(@sDataRow, ',') + 1)

                   /* 
                   *   SSMA error messages:
                   *   O2SS0050: Conversion of identifier 'regexp_replace(VARCHAR2, CHAR)' is not supported.

                   SET @nCount2 = sysdb.ssma_oracle.length_varchar((NULL))
                   */
                END
    -----------------------------

    Thanks.


    Monday, March 2, 2015 2:45 PM
  • Hello,

    Stored procedures using to_date(VARCHAR2, CHAR) and regexp_replace(VARCHAR2, CHAR) change them to to_date(VARCHAR2, NCHAR) and regexp_replace(VARCHAR2, NCHAR).


    Could you try changing the following code

    dDate DATE;

    BEGIN

    SELECT SYSDATE INTO dDate FROM dual;


    To this?

    ddate date := sysdate;

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com



    Monday, March 2, 2015 2:58 PM
  • The exact statement in oracle side which causing this error (O2SS0050: Conversion of identifier 'to_date(VARCHAR2, CHAR)' is not supported.) is below:

    dStartDate:= to_date(sStartDate,'MON-YYYY');

    Statement causing error O2SS0050: Conversion of identifier 'regexp_replace(VARCHAR2, CHAR)' is not supported is below.

    nCount2:= length(regexp_replace(sDataRow,'[^,]'));

    So there is no statement which is using to_date(VARCHAR2, CHAR) and regexp_replace(VARCHAR2, CHAR) in as such. 'MON-YYYY'  and '[^,]' are CHAR values hence SSMA is unable to convert it from varchar2 to char.

    ---

    Regarding SYSDATE issue, you mean to put below code in target(SQL) side in SSMA ?

    dDate date := sysdate;

    Thanks.

    Wednesday, March 4, 2015 10:10 AM
  • Hello,

    Can you try changing that REGEXP_REPLACE by TRANSLATE or REPLACE? Is that possible?

    About the date error, please try the following workaround.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c9801b59-f3b8-47bd-a8ee-f0ac34a319ff/ssma-migration-from-oracle-date-errors?forum=sqlservermigration


    If you can use a date conversion that includes the day it should work. For example, 'dd-mon-yyyy'.

    About the sysdate, try assigning that value directly when you create the variable as shown in the following resource.

    http://blogs.msdn.com/b/ssma/archive/2011/05/12/migrating-oracle-to-sql-server-using-ssma-emulating-oracle-package-variables.aspx

    Hope this helps.



    Regards,



    Alberto Morillo
    SQLCoffee.com



    Wednesday, March 4, 2015 1:26 PM