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.

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