locked
how to add "Decode" to Columns type mapping RRS feed

  • Question

  • Hi everyone,

    I have a request to migrate Oracle 11gR2 to Sqlserver 2012. I am trying with SSMA for oracle 6.0 but i have a problem.

    All the tables from a specific Oracle schema have the follow description:

    CREATE TABLE xxxxx (

    COL1 ....,

    COL2.....,

    COL3.....,

    USER_NAME VARCHAR2(21) DEFAULT SUBSTRB(USER,1,20) NOT NULL,
    UNIT_IDENT VARCHAR2(13) DEFAULT sys_context('USERENV','DB_NAME') NOT NULL);

    And when I try to convert schema I get the same error for every table:

    O2SS0050: Conversion of Idenfier SUBSTRB(varchar2, binary_integer, binary_integer) is not supported

    and

    O2SS0050: Conversion of Idenfier SYS_context(char, char) is not supported.

    I have around 170 tables with the same structure, and i want to do the conversion automatically. 

    All answers will be apreciated, thanks in advance.

    Monday, June 8, 2015 12:29 PM

Answers

  • Hello,

    It seems to me that equivalent of sys_context('USERENV','DB_NAME') is DB_NAME(). Click here for more information.

    I don’t know an equivalent for substrb in SQL Server, because it calculates the length in bytes.

    Is it possible for you to programmatically remove the default constraints before using SSMA and later add them in SQL Server. You can remove them in Oracle like this

    ALTER TABLE YourTable MODIFY YourColumn DEFAULT NULL;



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, June 8, 2015 1:01 PM