locked
How to convert Oracle User defined datatype to SQL Server 2014 using SSMA Version 6.0 RRS feed

  • Question

  • I am trying to convert Oracle 11g OE schema to SQL Server 2014, using SSMA version 6.0

    Getting an error in converting the Oracle View to SQL Server

    CREATE OR REPLACE VIEW OC_CUSTOMERS OF OE.CUSTOMER_TYP WITH OBJECT IDENTIFIER (customer_id) AS
    SELECT c.customer_id, c.cust_first_name, c.cust_last_name, c.cust_address,
               c.phone_numbers,c.nls_language,c.nls_territory,c.credit_limit,
               c.cust_email,
               CAST(MULTISET(SELECT o.order_id, o.order_mode,
                                   MAKE_REF(oc_customers,o.customer_id),
                                   o.order_status,
                                   o.order_total,o.sales_rep_id,
                                   CAST(MULTISET(SELECT l.order_id,l.line_item_id,
                                                        l.unit_price,l.quantity,
                                                 MAKE_REF(oc_product_information,
                                                          l.product_id)
                                                 FROM order_items l
                                                 WHERE o.order_id = l.order_id)
                                        AS order_item_list_typ)
                             FROM orders o
                             WHERE c.customer_id = o.customer_id)
                    AS order_list_typ)
         FROM customers c
    ;

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

    /*
    *   SSMA error messages:
    *   O2SS0461: Conversion of object view is not supported.
    *   
    *   OF OE.CUSTOMER_TYP
    *      WITH OBJECT IDENTIFIER (customer_id)

    CREATE VIEW dbo.OC_CUSTOMERS
    AS
       /*Generated by SQL Server Migration Assistant for Oracle version 6.0.0.*/
       /*
       *   SSMA error messages:
       *   O2SS0481: Conversion of statement containing user defined type column 'c.cust_address' not supported.
       *   O2SS0481: Conversion of statement containing user defined type column 'c.phone_numbers' not supported.
       *   O2SS0430: Conversion of multiset conditions is not supported.
       *   CAST(MULTISET
       *   (
       *      SELECT
       *         o.order_id,
       *         o.order_mode,
       *         MAKE_REF(oc_customers, o.customer_id),
       *         o.order_status,
       *         o.order_total,
       *         o.sales_rep_id,
       *         CAST(MULTISET
       *            (
       *               SELECT
       *                  l.order_id,
       *                  l.line_item_id,
       *                  l.unit_price,
       *                  l.quantity,
       *                  MAKE_REF(oc_product_information, l.product_id)
       *               FROM order_items  l
       *               WHERE o.order_id = l.order_id
       *            ) AS order_item_list_typ)
       *      FROM orders  o
       *      WHERE c.customer_id = o.customer_id
       *   ) AS order_list_typ)

       SELECT
          c.CUSTOMER_ID,
          c.CUST_FIRST_NAME,
          c.CUST_LAST_NAME,
          c.cust_address,
          c.phone_numbers,
          c.NLS_LANGUAGE,
          c.NLS_TERRITORY,
          c.CREDIT_LIMIT,
          c.CUST_EMAIL,
          NULL
       FROM dbo.CUSTOMERS  AS c   */


    */

    Any suggestion on converting this view would be helpful.

    Kind regards.

    Tuesday, April 28, 2015 1:41 PM

All replies

  • Hi Venkatesana,

    Oracle supports User Defined Type (UDT) which include object type and collection. This data type is not supported in SQL Server and SSMA does not support conversion of Oracle UDT. You may consider using SQL Server Table Value Parameter (TVP) when migrating your Oracle database to SQL Server.

    In other words, you need to recreate the view with Transact-SQL statements in SQL Server. For more details, please check out the following references.
    Converting Oracle UDT to SQL Server TVP
    Migrating Oracle to SQL Server 2008 White Paper


    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Wednesday, April 29, 2015 2:31 AM
  • Thanks Lydia Zhang for your reference urls.

    Please let me know if you come across any reference articles related to

    Creating a SQL Server table and View with columns as SQL Server TVP similar to Oracle UDT like

      CREATE TABLE "OE"."CUSTOMERS"
       (    "CUSTOMER_ID" NUMBER(6,0),
        "CUST_FIRST_NAME" VARCHAR2(20 BYTE) CONSTRAINT "CUST_FNAME_NN" NOT NULL ENABLE,
        "CUST_LAST_NAME" VARCHAR2(20 BYTE) CONSTRAINT "CUST_LNAME_NN" NOT NULL ENABLE,
        "CUST_ADDRESS" "OE"."CUST_ADDRESS_TYP" ,
        "PHONE_NUMBERS" "OE"."PHONE_LIST_TYP" ,
        "NLS_LANGUAGE" VARCHAR2(3 BYTE),

    And also creating a SQL Server TVP inside an another SQL Server TVP as in Oracle

    create or replace TYPE customer_typ
     AS OBJECT
        ( customer_id        NUMBER(6)
        , cust_first_name    VARCHAR2(20)
        , cust_last_name     VARCHAR2(20)
        , cust_address       cust_address_typ

       , credit_limit       NUMBER(9,2)
        , cust_email         VARCHAR2(30)
        , cust_orders        order_list_typ
        )
    NOT FINAL;

    Kind regards.

    Venkatesha


    Wednesday, April 29, 2015 3:43 PM
  • Ask your DBA to convert these Oracle scripts to SQL Server T-SQL.
    Friday, May 1, 2015 8:05 AM
  • Do you have a real scenario, or are you just playing around with the OE sample schema?

    Oralce object types are significantly different than SQL Server User Defined Table Types.

    Oracle object types are also obscure and rarely used in the real world.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, May 1, 2015 3:53 PM